Here is are my columns and measures in a Pivot table
Retailer : Apple Verizon Sprint
50 50 50
( Should show subtracted total from collapsed dimension below)
Sales: + online +store + online +store + online + store
20 70 20 70 20 70
Instead it keeps showing 70.
How do I write expression to show subtracted total instead?
Just to revarify, you are expecting result like this:
and data is something like:
Please correct me, if i am wrong.
You can use the Dimensionality() function to detect that you are on the Total row and perform a different calculation. Dimensionality() = 1 for the total row
if(Dimensionality()=1, do total calc, sum(Sales))
This is not a total row.
Collapsed pivot shows : Online sales values : -70 and -20 the calc here is min ( Online Sales) - this is the only way it shows the correct values not sum
Closed pivot should shows : -50 on top
Ok, so I misused the term "Total". Total rows have Dimensionality()=0. But the first Dimension (Retailer) will have Dimensionality()=1. Add "Dimensionality()" as a Measure to your table to see how it works