Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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
1 | Line_1 | NULL | 325 | 01.10.2000 | 10 | 10 | NULL |
2 | Line_2 | Detail_1 | 325 | 02.10.2000 | 10 | 14 | 10 |
2 | Line_2 | Detail_2 | 325 | 03.10.2000 | 10 | 14 | 4 |
3 | Line_3 | Detail_3 | 325 | 01.12.2000 | 12 | 12 | 12 |
4 | Line_4 | NULL | 987 | 01.12.2000 | 12 | 11 | NULL |
4 | Line_4 | Detail_4 | 987 | 02.12.2000 | 12 | 11 | 10 |
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_1 | 325 | 10 | 10 |
Line_2 | 325 | 10 | 14 |
Line_3 | 325 | 12 | 12 |
Line_4 | 987 | 12 | 11 |
Then group by Item_code and Month
Item_codeMonthOrderedQty
325 | 10 | 24 |
325 | 12 | 12 |
987 | 12 | 11 |
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.
@volesh ,
You may take a look at the post below.
https://community.powerbi.com/t5/Desktop/Create-a-bar-graph-with-measures-as-axis/td-p/455900
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
User | Count |
---|---|
89 | |
86 | |
75 | |
69 | |
68 |
User | Count |
---|---|
220 | |
128 | |
117 | |
82 | |
77 |