Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all
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?
try below link approach
Claudia,
Just to revarify, you are expecting result like this:
and data is something like:
Retailer | Sales | By |
Apple | 20 | Online |
Apple | 30 | Store |
Verizon | 20 | Online |
Sprint | 20 | Online |
Verizon | 30 | Store |
Sprint | 30 | Store |
Please correct me, if i am wrong.
Yes but you are totaling 20 + 30
My data is -70 and -20 and I need to show - 50. Instead it keeps showing - 70
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
Something like:
if(Dimensionality()=1, do total calc, sum(Sales))
-Rob
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
-Rob