Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Building Price and Consumption effect

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 

Data Protection
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    5 years ago

     

     

    [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-NUMBERS/td-p/1904161

     

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

     

     

    [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-NUMBERS/td-p/1904161