How to calculate dimension total in pivot table ?

[claudialet]

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?

[dplr-rn]

try below link approach

Missing Manual - Before() and After()

[amit_gupta]

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.

[claudialet]

Yes but you are totaling  20 + 30

My data is  -70  and -20  and I need to show  - 50. Instead it keeps showing - 70

[rwunderlich]

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

[claudialet]

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

[rwunderlich]

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