Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get 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

Reply
moia79
Helper I
Helper I

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

SKUActuals for FCAFCST for FCA lag 3MFCA lag 3 M
129218360036075.00%
12375372400108462.20%
123503515010883.70%
1217207110037851.20%
121720680028852.90%
121720494054073.00%
12172033200315099.20%
12171873000190877.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)

 

SKUActuals for FCAFCST for FCA lag 3MFCA lag 3 MFCA lag 3 M (AVG)
129218360036075.00%71%
12375372400108462.20%70%
123503515010883.70%71%
1217207110037851.20%75%
121720680028852.90%71%
121720494054073.00%75%
12172033200315099.20%78%
12171873000190877.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 1
amitchandak
Super User
Super User

@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

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.