Building Price and Consumption effect

[Anonymous]

Hello!

I'm building an financial report on power BI, and I'm struggling to calculate price and consumption effects for different products.

The price effect is calculated by: (ActualPrice - BudgetPrice) * ActualConsumption. And the Consumption Effect is calculated
by: (ActualCons. - BudgetCons.) * ActualPrice. I need two measures, one for price effect and the other for consumption effect.

@Jihwan_Kim @amitchandak @Fowmy @Anonymous 

[Anonymous · Accepted solution]

[Price Effect] =
SUMX(
    DISTINCT( T[Product] ),
    var PriceDelta = 
        CALCULATE(
            sum( T[Actual] ) - sum( T[Budget] ),
            // You should never slice by
            // this column. It should
            // be hidden.
            T[Information] = "Price"
        )
    var ActualConsumption =
        CALCULATE(
            sum( T[Actual] ),
            T[Information] = "Consump."
        )
    var Result = PriceDelta * ActualConsumption
    return
        Result
)

[Consumption Effect] =
SUMX(
    DISTINCT( T[Product] ),
    var ConsumptionDelta = 
        CALCULATE(
            sum( T[Actual] ) - sum( T[Budget] ),
            // You should never slice by
            // this column. It should
            // be hidden.
            T[Information] = "Consump."
        )
    var ActualPrice =
        CALCULATE(
            sum( T[Actual] ),
            T[Information] = "Price"
        )
    var Result = ConsumptionDelta * ActualPrice
    return
        Result
)

Please watch the WARNING about one-table models:

https://community.powerbi.com/t5/DAX-Commands-and-Tips/Why-one-table-models-will-produce-WRONG-NUMBE...