Dynamic ABC Classification 07-15-2019 13:18 PM

[Greg_Deckler]

This quick measure demonstrates how to implement ABC Classification as a measure. This allows for dynamic ABC classification that is contextually aware based upon filters. 

The attached PBIX and report implements the ABC classification based upon columns as demonstrated here: https://www.daxpatterns.com/abc-classification/ for comparison.

Note that the ABC classification for the column-based method is static even when the slicer is applied but the measure-based ABC classification is dynamic and takes the slicer filters into account. The measure formula is as follows:

mABC Class = 
VAR __salesTable = ADDCOLUMNS(ALLSELECTED('Sales SalesOrderDetail'),"__TotalSale",[OrderQty]*[UnitPrice])
VAR __salesTable1 = GROUPBY(__salesTable,[ProductID],"__ProductSales",SUMX(CURRENTGROUP(),[__TotalSale]))
VAR __salesTable2 = ADDCOLUMNS(__salesTable1,"__CumulatedSales",SUMX(FILTER(__salesTable1,[__ProductSales]>=EARLIER([__ProductSales])),[__ProductSales]))
VAR __totalProductSales = SUMX(__salesTable1,[__ProductSales])
VAR __salesTable3 = ADDCOLUMNS(__salesTable2,"__CumulatedPercentage",DIVIDE([__CumulatedSales],__totalProductSales,0))
VAR __salesTable4 = ADDCOLUMNS(__salesTable3,"__ABC Class",SWITCH(TRUE(),[__CumulatedPercentage]<=0.7,"A",[__CumulatedPercentage]<=0.9,"B","C"))
VAR __salesTable5 = FILTER(__salesTable4,[ProductID] = MAX('Production Product'[ProductID]))
RETURN MAXX(__salesTable5,[__ABC Class])

eyJrIjoiYzU2MDVjNDItMzQ0My00Y2RiLTkzNjUtNTNlMjFhMGU3OTkwIiwidCI6IjRhMDQyNzQzLTM3M2EtNDNkMi04MjdiLTAwM2Y0YzdiYTFlNSIsImMiOjN9

[SamTaylor]

Hi Greg,

I am looing to try the Dynamic ABC Classification. My starting question is I have a table full of sales, but I only need to ABC for selection of the sales.  I have a Date Season and Product Season I will use to filter the data.  So when I create the first Var_SalesTable, how do I reduce the data selected to just the sales I need.  Cheers Sam

[gkaloferov]

@Greg_Deckler  Hi Greg, Did you manage to achieve the above-mentioned ABC technique by using Date slicer and second measure for sameperiodlastyear?

For example,

The ABCclass of "Product 123" in Year 2021/Quarter 1 was A

The ABCclass of "Product 123" in Year 2020/Quarter 1 (SAME PERIOD LAST YEAR) was B

and then both measures to be used simultaneously in a Table?

[Greg_Deckler]

@gkaloferov Not sure I entirely understand. There is a much improved version of this measure in my book, DAX Cookbook and you can find the chapter PBIX files on GitHub. First recipe in Chapter 12. And it would be unlike me to use SAMEPERIODLASTYEAR because I hate those functions. https://community.powerbi.com/t5/Community-Blog/To-bleep-With-Time-Intelligence/ba-p/1260000

[gkaloferov]

@Greg_Deckler thank you for your time and for the answer. I recognize my mistake that I had to initially provide more information about the desired effect, but anyway, here's what I want to achieve as a result:

I am trying to achieve а dynamic (responsive to date and other features) ABC customer classification together with time comparison indicators, namely, classification of customers based on the currently selected period but also I want to show in the second column of the table what was the class during the same period last year. I used the term SAMEPERIODLASTYEAR (not the function) only to represent the result I was looking for, without thinking that this was the right approach.

If I have to be more detailed, I want to achieve something like a combination of your dynamic approach (which is much faster than that of SQLBI guys) and the snapshot approach of SQLBI, described here: https://www.daxpatterns.com/abc-classification/

I looked at your example in github and I think that you really improved it a lot, but I can't figure out how to make it dynamic for more than 1 period.

Something like this:


Regards,
Georgi

[sirros_iot]

Nice report @Greg_Deckler !

I would like to know if there's a way of counting the number os products on each classification to put in a overall table, in a dynamic way. Also, it would be awesome if we could filter the classification, selecting A, B or C. I'm having troubles with this topics.. 

Best Regards,
Diego S. Lutckmeier

[jskipper]

I would also like to achieve this.

Is it possible?