cancel
Showing results for 
Search instead for 
Did you mean: 

Dynamic ABC Classification

Super User
2244 Views
Super User
Super User

Dynamic ABC Classification

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])

 


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


sirros_iot Member
Member

Re: Dynamic ABC Classification

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