Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello All,
I am calculating abs differnce between actual and fcst at SKU level which rollups upto different product groups. I want to roll up abs diff of all items correctly at product group level but right now it sums up and then does abs diff.I saw couple of links but in my case data is coming from 3 tables not one.
Sample Data
Total Sales Fcst Expected(Abs diff) My output
Product Group 300 270 90 30
Item1 100 50 50 50
Item2 200 240 40 40
Data is coming from 3 tables- Product info from DimProduct, Sales from Salestable and Forecast from FinalPredictions table all joined by ItemNumber which is common join between tables
My measure:
Solved! Go to Solution.
Hello @Fuhrer786
Give this a try. It will generate a virtual table of all the [Product Group] in your DimProduct table then, for each row in the virtual table it will do the ABS calc and finally it will add up all those values.
Measure = SUMX ( VALUES ( DimProduct[Product Group] ), ABS ( CALCULATE ( SUM ( SalesTable[Totalsales] ) ) - CALCULATE ( SUM ( FinalPredictions[Predictedsales] ) ) ) )
Hello @Fuhrer786
No, you are not doing anything wrong. I forgot to force the context transition in the iterator. It should have been.
Measure = SUMX ( VALUES ( DimProduct[Product Group] ), ABS ( CALCULATE ( SUM ( SalesTable[Totalsales] ) ) - CALCULATE ( SUM ( FinalPredictions[Predictedsales] ) ) ) )
But two measures is the way to do it because they force the context transition and can be used in other places.
Hello @Fuhrer786
Give this a try. It will generate a virtual table of all the [Product Group] in your DimProduct table then, for each row in the virtual table it will do the ABS calc and finally it will add up all those values.
Measure = SUMX ( VALUES ( DimProduct[Product Group] ), ABS ( CALCULATE ( SUM ( SalesTable[Totalsales] ) ) - CALCULATE ( SUM ( FinalPredictions[Predictedsales] ) ) ) )
Hello @jdbuchanan71
It worked perfect now. I created 2 separate measures for actualsales and forecast value
TotalSales=sum(Salestable[TotalSales])
TotalForecast=sum(Finalpredictions[PredictedSales])
Measure = SUMX ( VALUES ( DimProduct[Item Number] ), ABS (TotalSales - TotalForecast ) )
Formula still same, I must be doing something wrong but creating extra measures worked for me! Thank u so much
Hello @Fuhrer786
No, you are not doing anything wrong. I forgot to force the context transition in the iterator. It should have been.
Measure = SUMX ( VALUES ( DimProduct[Product Group] ), ABS ( CALCULATE ( SUM ( SalesTable[Totalsales] ) ) - CALCULATE ( SUM ( FinalPredictions[Predictedsales] ) ) ) )
But two measures is the way to do it because they force the context transition and can be used in other places.
Hello @jdbuchanan71
Thanks for efforts. I tried but didn't work. Also, I have to do at item level and roll it up to product groups. I tried with below one after changing product group to item number in sumx
Measure = SUMX ( VALUES ( DimProduct[Item Number] ), ABS ( SUM ( SalesTable[Totalsales] ) - SUM ( FinalPredictions[Predictedsales] ) ) )
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
122 | |
101 | |
71 | |
61 |