Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Creating variance % in Tabular Editor using calculated groups

Hi all,

Not sure if this is the right place to post, we are using calculated groups in Tabular Editor with Power BI. We are trying to create variance % calcuation group for Gross Profit.

 

I've created a measure within Power BI for the variance:

[Actual NZD] - [Budget NZD]

 

My calculations from Tabular Editor for individual Revenue and Gross Profit: 

CALCULATE( SELECTEDMEASURE(),
    'Account'[Account Level 9 Text] IN { "Total Revenue" }
) / 1000
CALCULATE( SELECTEDMEASURE(),
    'Account'[Account Level 9 Text] IN { "Total Gross Profit" }
) / 1000

 

My calcuation for the Gross Profit %:

DIVIDE( CALCULATE( SELECTEDMEASURE(), 
    'Measure Selector'[Measure Selector] = "Total Gross Profit" ),
    CALCULATE( SELECTEDMEASURE(), 
    'Measure Selector'[Measure Selector] = "Total Revenue" ) )

 

When I publish to the Power BI report it doesn't calculate properly. It looks like it is calcuating by the column for "Var Act v Bud" rather than the row. The 128% should be 63.9%. 

 

How do I get this to calculate correctly?

 

Thanks for your help

 

 

 

Need Help

4 Replies

  • parry2k's avatar
    parry2k
    Super User
    6 years ago

    Anonymous try this

     

    DIVIDE( CALCULATE( SELECTEDMEASURE(), 
'Account'[Account Level 9 Text] IN { "Total Gross Profit" }
 
    CALCULATE( SELECTEDMEASURE(),     'Account'[Account Level 9 Text] IN { "Total Revenue" }
     )

     

    I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos 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.⚡

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      parry2k Thanks for the reply. Unfortunley it's still not quite right. The actual and budget is calculating propely, but not the variance. 

      (Had to export to Excel to show some fake numbers)

       

      • parry2k's avatar
        parry2k
        Super User
        6 years ago

        Anonymous is it possible to send some sample data, I would like to test it and get back to you.

         

        I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos 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.⚡