Calculate the average of %Difference

[romoguy15]

Hello Everyone,

I have some data of account #s for 2019 and 2020. Each account has a column showing the Actual amount and the Forecasted amount. I've created a measure that gives me the difference of Actual - Forecast, and another measure that gives me the % Difference to Forecast. What I am trying to accomplish is to get the average of the % Difference to Forecast.

Link to the report.

https://1drv.ms/u/s!AqID1H0nHPOzg3-gmfHjrJMB0iXs?e=n6YZy2

Thank you in advance,

Andrei

[romoguy15]

@parry2k I figured out the difference. The test report has a Many to One relationship between calendar and Summary. But my real data report has a Many to Many Relationship therefor not allowing the "summarize" function to work. Any work around to this?

[parry2k]

@romoguy15 basically creating a virtual table by using summarize and "@delta" is a name for the existing measure you can call it anything and then we are doing an average on it.

I hope it helps.

Check my latest blog post Compare Budgeted Scenarios vs. Actuals I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[parry2k · Accepted solution]

@romoguy15 try this mesure

Avg = AVERAGEX ( SUMMARIZE ( Summary, Summary[Account],  'Calendar'[Year], "@Delta", [% Delta to Forecast] ), [@Delta] )

Check my latest blog post Compare Budgeted Scenarios vs. Actuals I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[romoguy15]

Hey Parry2k,

For some reason it works perfectly on the test report. But when I plugged it into my actual report it is not working.  I am getting this error

The column 'Year' specified in the 'SUMMARIZE' function was not found in the input table.

This doesn't make sense. The data is pretty much the same as far as my relationship with the calendar and the Summary dataset. Nothing really changed. Could you help?

[romoguy15]

Hi, thank you for the quick solution. Would you mind explaining the use of "@delta" ?  I want to understand how this measure works with that into the formula.