Forum Discussion
Calculate average on aggregated row
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
1 Reply
- amitchandak
Super User
Anonymous , 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
