Forum Discussion

romoguy15's avatar
romoguy15
Icon for Helper IV rankHelper IV
5 years ago
Solved

Calculate the average of %Difference

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 differenc...
Need Help
  • parry2k's avatar
    parry2k
    5 years ago

    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.⚡

parry2k's avatar
parry2k
Icon for Super User rankSuper User
5 years ago

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's avatar
    romoguy15
    Icon for Helper IV rankHelper IV
    5 years ago

    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.

  • romoguy15's avatar
    romoguy15
    Icon for Helper IV rankHelper IV
    5 years ago

    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?