08-02-2018 10:32 AM
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])
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..
Diego S. Lutckmeier