Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowGet certified as a Fabric Data Engineer: Check your eligibility for a 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700. Get started
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
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
User | Count |
---|---|
146 | |
72 | |
63 | |
52 | |
51 |
User | Count |
---|---|
208 | |
91 | |
62 | |
59 | |
56 |