Difference of dimensions between measures

[Anonymous]

Hi All, 

I have calculated two measures gross margin and profit margin as above. 

I have a dimension in my data set "actual" and "plan". I am able to place the actual and plan in the column but i am unsure of how to put a variance (actual - plan) into the column beside "plan". Is there a way to do this? 

Thanks for your help

[amitchandak]

How you create the current view.  Can you share calculations.

[Anonymous]

So gross profit is calculated as profit/revenue and gross margin is cost/revenue. The version is a column in my dataset containing "actual" and "plan".  

[v-kelly-msft · Accepted solution]

Hi @Anonymous ,

You need to create 3 measures as below:

Gross Margin = CALCULATE(MIN('Table'[Value]),ALLEXCEPT('Table','Table'[Category]))

Profit = CALCULATE(MAX('Table'[Value]),ALLEXCEPT('Table','Table'[Category]))

result = 
VAR actual =
    CALCULATE (
        SWITCH (
            SELECTEDVALUE ( 'Table (2)'[row] ),
            "Gross Margin", 'Table'[Gross Margin],
            "Profit", 'Table'[Profit]
        ),
        'Table'[Category] = "actual"
    )
VAR plan =
    CALCULATE (
        SWITCH (
            SELECTEDVALUE ( 'Table (2)'[row] ),
            "Gross Margin", 'Table'[Gross Margin],
            "Profit", 'Table'[Profit]
        ),
        'Table'[Category] = "plan"
    )
RETURN
    SWITCH (
        SELECTEDVALUE ( 'Column'[Column] ),
        "actual", actual,
        "plan", plan,
        plan - actual
    )

and another 2 tables showed as below:

Table ColumnTable Table(2)

Finally you will see:

For the related .pbix file,pls click here.

Best Regards,

Kelly

[amitchandak]

This one is tricky. Create a diff column. for these you need to use actual and plan as filter. 

The using summarize and union , you have to merge the current result with new columns.

[Mariusz]

Hi @Anonymous 

You can create a measure Variance = [actual] - [plan]

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.