Dynamic ABC Analysis in PowerPivot using DAX

I recently moved my blog to a new host. It is now hosted at



THIS specific post can be found at:


In case you decide to follow my blog please ensure that you follow the new Blog!




An ABC Analysis is a very common requirement for for business users. It classifies e.g. Items, Products or Customers into groups based on their sales and how much impact they had on the cumulated overall sales. This is done in several steps.

1) Order products by their sales in descending order
2) Cumulate the sales beginning with the best selling product till the current product
3) Calculate the percentage of the cumulated sales vs. total sales
4) Assign a Class according to the cumulated percentage

Marco Russo already blogged about this here. He does the classification in a calculated column based on the overall sales of each product. As calculated columns are processed when the data is loaded, this is not dynamic in terms of your filters that you may apply in the final report. If, for example, a customer was within Class A regarding total sales but had no sales last year then a report for last year that uses this classification may give you misleading results.

In this blog I will show how to do this kind of calculation on-the-fly always in the context of the current filters. I am using Adventure Works DW 2008 R2 (download) as my sample data and create a dynamic ABC analysis of the products.

The first thing we notice is that our product table is a slowly changing dimension of type 2 and there are several entries for the same product as every change is traced in the same table.


So we want to do our classification on the ProductAlternateKey (=Business Key) column instead of our ProductKey (=Surrogate Key) column.

First we have to create a ranking of our products:

Rank CurrentProducts:=IF(HASONEVALUE(DimProduct[ProductAlternateKey]),
        [SUM SA])))

Check if there is only one product in the current context and that this product also has sales. If this is the case we calculate our rank. We need to do the CALCULATETABLE to do the ranking within the currently applied filters on other columns of the DimProduct-table e.g. if a filter is applied to DimProduct[ProductSubcategoryKey] we want to see our ranking within that selected Subcategory and not against all Products.

I also created a measure [SUM SA] just to simplify the following expressions:

SUM SA:=SUM(FactInternetSales[SalesAmount])


The second step is to create a running total starting with the best-selling product/the product with the lowest rank:

CumSA CurrentProducts:=SUMX(
        [Rank CurrentProducts],
        [SUM SA]),
    [SUM SA])

We use a combination of SUMX() and TOPN() here. TOPN() returns the top products ordered by [SUM SA]. Further we use our previously calculated rank to only get the products that have the same or more sales than the current product. For example if the current product has rank 3 we sum up the top 3 products to get our cumulated sum (=sum of the first 3 products) for this product. Again we need to use CALCULATETABLE() to retain other filters on the DimProduct-table.


The third step is pretty easy – we need to calculate percentage of the cumulated sales vs. the total sales:

CumSA% CurrentProducts:=
[CumSA CurrentProducts]
CALCULATE([SUM SA], ALL(DimProduct[ProductAlternateKey]))

This calculation is straight forward and should not need any further explanation.

The result of those calculations can be seen here:



To do our final classification we have to extend our model with a new table that holds our classes and their border-values:

Class LowerBoundary UpperBoundary
A 0 0.7
B 0.7 0.9
C 0.9 1

Class A should contain products which’s cumulated sales are between 0 and 0.7 – between 0% and 70%.
Class B should contain products which’s cumulated sales are between 0.7 and 0.9 – between 70% and 90%.

(This table can later be extended to support any number of classes and any boundaries between 0% and 100%.)

To get the boundaries of the selected class we create two measures that are later used in our final calculation:




Our final calculation looks like this:

SA Classified Current:=IF(NOT(ISCROSSFILTERED(Classification[Class])),
    [SUM SA],
        [MinLowerBoundary] < [CumSA% CurrentProducts]
            && [CumSA% CurrentProducts] <= [MaxUpperBoundary])))

If our Classification-table is not filtered, we just show our [SUM SA]-measure. Otherwise we extend the filter on our DimProduct[ProductAlternateKey] using our classification filtering out all products that do not fall within the borders of the currently selected class.

This measure allows us to see the changes of the classification of a specific product e.g. over time:


In 2006 our selected product was in Class C. For 2007 and 2008 it improved and is now in Class A. Still, overall it resides in Class B.

We may also analyze the impact of our promotions on the sales of our classified products:


Our Promotion “Touring-1000 Promotion” only had impact on products in Class C so we may consider to stop that promotion and invest more into the other promotions that affect all classes.


The classification can be used everywhere you need it – in the filter, on rows or on columns, even slicers work. The only drawback is that the on-the-fly calculation can take quite some time. If I find some time in the future i may try to further tune them and update this blog-post.


The example workbook can be downloaded here:

Though it is already in Office 2013 format an may not be opened with any previous versions of Excel/PowerPivot.
It also includes a second set of calculations that use the same logic as described above but does all the calculations without retaining any filters on the DimProducts-table. This allows you to filter on Class “A” and ProductSubcategory “Bike Racks” realizing that “Bike Racks” are not a Class “A” product or to see which Subcategories or Categories actually contain Class A, B or C products!

19 Responses to Dynamic ABC Analysis in PowerPivot using DAX

  1. David Hager says:

    Your running total methodology obtained with TOPN is very creative. It opens up a number of interesting possibilities, not the least of which is a new way to calculate moving averages.

  2. Vitaly Popov says:

    I tried your solution. on my data (3,000,000 + lines of sales and 20,000 + products) measure CumSA CurrentProducts is calculated for too long. There are ideas for increasing productivity?

    • gbrueckl says:

      20k products is quite a lot for this kind of calculation
      the only option I see to improve performance is to remove products that do not have sales at all before running all the calculations
      running the calculation only on a subset of data should speed it up but this also depends on how sparse your fact-table is populated

      how is the calculation for [Rank CurrentProducts] performing?
      if it performs well you may consider to do the classification only based on the rank
      e.g. Top 1000 products is Class A, 1001-5000 is Class B and rest is Class C

  3. Vitaly Popov says:

    Thanks, Gerhard.
    Needs is a classic ABC. I have the solution to the Multidimension. I’m using a dynamic set and function TopPercent to determine the minimum value of sales for the product in 80% (A Class), 95% (B Class) and then compare the sales of each product with these values.
    iif([Measures].[SalesAmount]>=[minA SalesAmount],”A”,
    iif([Measures].[SalesAmount]>=[minB SalesAmount],”B”,”C”))

    In DAX no TopPercent function…

    • gbrueckl says:

      I tried to tune the calculation by also calculating [minA SalesAmount] first but the performance is worse than before as that calculation is also performed everytime

      the only option I see is to precacluated those [minA] values for predefined slices – e.g. Year and Country:

      Year Country MinA MinB MinC
      2008 US 300.000 100.000 0
      2009 UK 175.000 100.000 0

      [MinA]/[MinB]/[MinC] could be calculated columns that do the expensive calculation at beforehand so no on-the-fly calculation is needed

      but this makes the whole thing much more complex and also it is not flexible anymore
      though, for your case it may be a viable solution

  4. pscorca says:

    Hi Gerhard, your sample is a very good. Unfortunately I’ve yet Excel 2010 and so I cannot open the data model: the Classes table isn’t related to any other table, isn’t it? If I want to analyse by category, do I modify your formulas or is it better to create new measures?

    • gbrueckl says:

      Hi pscorca,
      you are right, Classes table is not related to any other table
      to analyze by category it is probably easier to change the current calculations instead of creating new ones

  5. Hi,

    Thank you for sharing the solution above. I am just getting stuck at one place – the [Rank]. This is the RANK formula I am using in the Pivot Table is

    =IF(NOT(ISBLANK([Sessions conducted])),
    VALUES(Feedback[Organised by]),
    ALL(Feedback[Organised by])),
    [sessions conducted]))

    The problem is that if multiple clients give me the same number of sessions, then it assigns the same rank. So if 3 clients give me 10 sessions each, then the rank will be 5 for all three. Is there a way to get the rank as 5,6 and 7 respectively.

    The RANK.EQ will not work here because the second argument to the RANK function has to be a column name and not a measure.

    Please help.

    • gbrueckl says:

      Hi Ashish,
      I also replied to your MSDN post already
      the RANKX()-function accepts a total of 5 arguments to handle ties and ordering

      =IF(NOT(ISBLANK([Sessions conducted])),
      VALUES(Feedback[Organised by]),
      ALL(Feedback[Organised by])),
      [sessions conducted],
      [sessions conducted],

      does this work for you?

  6. Hi,

    Thank you for replying but that does not work. The ranks assigned to same numbers is still the same.

    • gbrueckl says:

      could you share your workbook or send it to me per mail so i can take a closer look at the Problem?
      you find my mail in the About-Tab on top of the page

  7. Pingback: ABC analysis

  8. Van Phan says:

    Hi Gerhard,

    I am working on the Top 10/ 20/30/50 Products and rank 31-50, 51-10 etc and its revenue contribute to total revenue (how many %). I use the similar method but I have spent so much time on this but it does not work for me. It would be appreciated if you could help me with this.

  9. Erik Svensen says:

    Hi Gerhard,

    Thank you for sharing… I used your method to Group stores by Sales Value Growth and it worked perfectly.


  10. Cip says:

    Hi Gerhard,

    I need to set the rank of products over years as equal to the rank of the last year selected by the user in a timeline.
    For Example: if user selects years from 2009 to 2012, 2009/2010/2011 ranks should be equal to 2012 rank.

    Can you help me?


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


Get every new post delivered to your Inbox.

Join 46 other followers

%d bloggers like this: