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 cr...
Need Help
parry2k's avatar
parry2k
Icon for Super User rankSuper 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
      Icon for Super User rankSuper 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.⚡

    • v-juanli-msft's avatar
      v-juanli-msft
      Icon for Community Support rankCommunity Support
      6 years ago

      Hi Anonymous 

      If you have data below, create measures

      actual_number = CALCULATE(SUM('Table 4'[Value]),FILTER('Table 4','Table 4'[cate]="actual"))

budget_number = CALCULATE(SUM('Table 4'[Value]),FILTER('Table 4','Table 4'[cate]="budget"))

total =
DIVIDE (
    CALCULATE (
        SUM ( 'Table 4'[Value] ),
        FILTER ( 'Table 4', 'Table 4'[account level] = "gross profit" )
    ),
    CALCULATE (
        SUM ( 'Table 4'[Value] ),
        FILTER ( 'Table 4', 'Table 4'[account level] = "revenue" )
    )
)


vs_number = [budget_number]-[actual_number]

actual _text =
IF (
    ISINSCOPE ( 'Table 4'[account level] ),
    FORMAT ( [actual_number], "General Number" ),
    FORMAT ( CALCULATE ( [total], 'Table 4'[cate] = "actual" ), "Percent" )
)

budget_text =
IF (
    ISINSCOPE ( 'Table 4'[account level] ),
    FORMAT ( [budget_number], "General Number" ),
    FORMAT ( CALCULATE ( [total], 'Table 4'[cate] = "budget" ), "Percent" )
)


vs_text =
IF (
    ISINSCOPE ( 'Table 4'[account level] ),
    FORMAT ( [budget_number] - [actual_number], "General Number" ),
    FORMAT (
        CALCULATE ( [total], 'Table 4'[cate] = "actual" )
            - CALCULATE ( [total], FILTER ( 'Table 4', 'Table 4'[cate] = "budget" ) ),
        "Percent"
    )
)

       

      Best Regards
      Maggie
      Community Support Team _ Maggie Li
      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.