Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a pivot table with two columns called Sales and Cost. Profit = Sales - Cost but still not working. What I am doing wrong?
Here's a screen shot:
The calculation itself is correct. That you didn't get the wanted result is caused from your condition which isn't fulfilled on the total-level and considering the Cost of 50 for Sales3.
You could adjust it by wrapping the calculation with an aggr() to enforce a summing against the row-level. This may look like:
sum(aggr(rangemax(sum(Sales)-sum(Cost), 0), RegionName, LineName))
and to enable the logic against the dimension-groups you need to query the active group. This means for example replacing the dimension of RegionName with:
[$(=getcurrentfield(YourGroupName))]
Works, thanks for your help