Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

XYZ analysis

Hi All,
I need to make XYZ analysis but I have some problem with a calculated column.
First of all, I have the dataset
order_idLines_IdDetail_IdItem_codeOrderDateMonthOrderedQtyShipQty

1Line_1NULL32501.10.20001010NULL
2Line_2Detail_132502.10.2000101410
2Line_2Detail_232503.10.200010144
3Line_3Detail_332501.12.2000121212
4Line_4NULL98701.12.20001211NULL
4Line_4Detail_498702.12.2000121110


As you can see, Order 2 has two rows and Order 4 has two rows, but just one value OrderedQuantity I have to use (DISTINCT Lines_Id).
For XYZ I have to calculate STDEV and AVERAGE and I have no problem with the measure
XYZ =
VAR Table_ =
ADDCOLUMNS(SUMMARIZE('Orders','Orders'[Item_code],'Orders'[Month]),
"QuantityBy",CALCULATE ( SUMX(DISTINCT('Orders'[Lines_Id]),CALCULATE(AVERAGE('Orders'[Ordered Qty])) )))
VAR Variation = DIVIDE (STDEVX.P ( Table_,[QuantityBy] ),AVERAGEX ( Table_,[QuantityBy] ),0 )
return Variation
But the report will use XYZ classes for the slicer and with the measure I can't do it.
So, I have to calculate SUM (Ordered Qty) by DISTINCT Lines_Id group by Item_code and Month,
and STDEV + AVERAGE in calc column.
With different steps I imagine this like:
group by Lines_Id
Lines_IdItem_codeMonthOrderedQty

Line_13251010
Line_23251014
Line_33251212
Line_49871211

Then group by Item_code and Month
Item_codeMonthOrderedQty

3251024
3251212
9871211

Am I right? How can I do it?

I am confused and ask for your help.
The expected result has an additional column with XYZ class.