Dynamic ABC Classification 08-02-2018 10:32 AM

[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