Creating variance % in Tabular Editor using calculated groups

[Anonymous]

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

[parry2k]

@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]

@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)

[v-juanli-msft]

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.

[parry2k]

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