Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I want to show target vs actual in a pivot table.
I have 2 tables: 1 with actuals, 1 with the targets (target margin %, target amount)
I combine these in a fact-table.
[
TargetActual
Product
Amount
Cost
Target margin pct
]
Now I want to show Actual and KPI next to each other based on the dimension "TargetActual" in 1 expression
The problem is that the calculation for the actual margin (amount-cost)/amount is NULL.
So it does not show a pct value.
see example attached.
What is the solution for this?
Try using RangeSum, instead of + to sum the two expressions
RangeSum(
(
//Margin of Actual
(sum({$<TargetActual={'Actual'}>}Amount) - sum({$<TargetActual={'Actual'}>}Cost)) / sum({$<TargetActual={'Actual'}>}Amount)
)
,
(
//Target margin
sum({$<TargetActual={'Target'}>}[Target margin pct])/100
))
Try using RangeSum, instead of + to sum the two expressions
RangeSum(
(
//Margin of Actual
(sum({$<TargetActual={'Actual'}>}Amount) - sum({$<TargetActual={'Actual'}>}Cost)) / sum({$<TargetActual={'Actual'}>}Amount)
)
,
(
//Target margin
sum({$<TargetActual={'Target'}>}[Target margin pct])/100
))