Forum Discussion
Anonymous
5 years agoNot applicable
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...
- Anonymous5 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:
Anonymous
5 years agoNot applicable
[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: