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

 

v-juanli-msft's avatar
v-juanli-msft
Community 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.