Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
dear PowerBI Gurus
I am addressing a issue that am struggling since a while. I created a database grouping the Actuals and Forecast on a monthly bucket since I want to calculate the accuracy as ABS(Actuals - Forecast)/ABS(Actuals+Forecast).
the problem is that, when I create a table on Item level, it works correctly
SKU | Actuals for FCA | FCST for FCA lag 3M | FCA lag 3 M |
1292183 | 600 | 360 | 75.00% |
1237537 | 2400 | 1084 | 62.20% |
1235035 | 150 | 108 | 83.70% |
1217207 | 1100 | 378 | 51.20% |
1217206 | 800 | 288 | 52.90% |
1217204 | 940 | 540 | 73.00% |
1217203 | 3200 | 3150 | 99.20% |
1217187 | 3000 | 1908 | 77.80% |
however it does not work when I aggregate items according with the item group. I tried with AVERAGEX("demand database",ABS(Actuals - Forecast)/ABS(Actuals+Forecast)) but the formula gives a strange result (last column of below example)
SKU | Actuals for FCA | FCST for FCA lag 3M | FCA lag 3 M | FCA lag 3 M (AVG) |
1292183 | 600 | 360 | 75.00% | 71% |
1237537 | 2400 | 1084 | 62.20% | 70% |
1235035 | 150 | 108 | 83.70% | 71% |
1217207 | 1100 | 378 | 51.20% | 75% |
1217206 | 800 | 288 | 52.90% | 71% |
1217204 | 940 | 540 | 73.00% | 75% |
1217203 | 3200 | 3150 | 99.20% | 78% |
1217187 | 3000 | 1908 | 77.80% | 75% |
is there a better way to calculate the average accuracy than using AVERAGEX? is it possible somehow to instruct DAX to execute the calculation at item level always and then using the average on aggregated level?
thanks a lot
@moia79 , refer if window function can help
Power BI Window function Rolling, Cumulative/Running Total, WTD, MTD, QTD, YTD, FYTD: https://youtu.be/nxc_IWl-tTc
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
108 | |
98 | |
78 | |
66 | |
53 |
User | Count |
---|---|
139 | |
100 | |
95 | |
85 | |
63 |