6 Replies Latest reply: Oct 10, 2018 2:19 PM by Rob Wunderlich

How to calculate dimension total in pivot table ?

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

How do I write expression to show subtracted total  instead?

• Re: How to calculate dimension total in pivot table ?

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.

• Re: How to calculate dimension total in pivot table ?

Yes but you are totaling  20 + 30

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

• Re: How to calculate dimension total in pivot table ?

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

• Re: How to calculate dimension total in pivot table ?

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

• Re: How to calculate dimension total in pivot table ?

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