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 - BudgetPr...
  • 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