Dynamic ABC analysis to be presented as a matrix

PC2790 — Tue, 25 Jul 2023 13:17:38 GMT

Hi,

I am doing ABC analysis in Power BI which is mainly done for inventory management. I have followed the superb article- https://www.daxpatterns.com/abc-classification/ which fulfills my requirement of making the calculations dynamic based on the filters applied by the user.

I am now stuck with the presentation part which is to be shown as:

		Consumption
		A	B	C	D
Stock	A	2979	55	5	345
	B	12	33	7	0
	C	1	1	5	1
	D	6	1	3	0

I am not sure how to achieve.

But, the solution is not very extensively given.

Can anyone help me here, please?

Here are the two measures I have used:

Dynamic_ABC STOCK Value = VAR StockValuebymaterialNo = CALCULATETABLE ( ADDCOLUMNS ( SUMMARIZE ( 'StockTable', 'StockTable'[Material No] ), "@MatStock", [TOT_STOCK_VALUE] ), ALLSELECTED ( 'StockTable' ) ) VAR ALLStockValue = CALCULATE ( [TOT_STOCK_VALUE], ALLSELECTED ( 'StockTable' ) ) VAR CumulatedPctByMatStock = ADDCOLUMNS ( StockValuebymaterialNo, "@CumulatedStock%", VAR CurrentStockValue = [@MatStock] VAR CumulatedStock = FILTER ( StockValuebymaterialNo, [@MatStock] >= CurrentStockValue ) VAR CumulatedStockValue = SUMX ( CumulatedStock, [@MatStock] ) VAR Perc = DIVIDE ( CumulatedStockValue, ALLStockValue ) RETURN MIN ( Perc, 1 ) -- Avoid >100% in case of rounding issues ) VAR MatInClass = FILTER ( CROSSJOIN ( CumulatedPctByMatStock, 'ABC Classes' ), AND ( [@CumulatedStock%] > 'ABC Classes'[Lower Boundary], [@CumulatedStock%] <= 'ABC Classes'[Upper Boundary] ) ) VAR Result = CALCULATE ( [TOT_STOCK_VALUE], KEEPFILTERS ( MatInClass ) ) RETURN Result

Dynamic_ABC Consumption Value = VAR ConsumptionValuebymaterialNo = CALCULATETABLE ( ADDCOLUMNS ( SUMMARIZE ( 'ConsumptionTable', 'ConsumptionTable'[Material No] ), "@MatConsumption", [AverageDailyConsumption] ), ALLSELECTED ( 'ConsumptionTable' ) ) VAR ALLConsumptionValue = CALCULATE ( [AverageDailyConsumption], ALLSELECTED ( 'ConsumptionTable' ) ) VAR CumulatedPctByMat = ADDCOLUMNS ( ConsumptionValuebymaterialNo, "@Cumulated%", VAR CurrentConsumptionValue = [@MatConsumption] VAR CumulatedConsumption = FILTER ( ConsumptionValuebymaterialNo, [@MatConsumption] >= CurrentConsumptionValue ) VAR CumulatedConsumptionValue = SUMX ( CumulatedConsumption, [@MatConsumption] ) VAR Perc = DIVIDE ( CumulatedConsumptionValue, ALLConsumptionValue ) RETURN MIN ( Perc, 1 ) -- Avoid >100% in case of rounding issues ) VAR MatInClass = FILTER ( CROSSJOIN ( CumulatedPctByMat, 'ABC Classes' ), AND ( [@Cumulated%] > 'ABC Classes'[Lower Boundary], [@Cumulated%] <= 'ABC Classes'[Upper Boundary] ) ) VAR Result = CALCULATE ( [AverageDailyConsumption], KEEPFILTERS ( MatInClass ) ) RETURN Result

topic Dynamic ABC analysis to be presented as a matrix in DAX Commands and Tips

Dynamic ABC analysis to be presented as a matrix