Fixing total in pivot

[#1]

Hi!

I have a pivot chart where I show gross, refunds and net (with partial sums). No problem there.

As dimension I use Group which values are gross, refund and net. Net is calculated as gross - refund.

The problem occur when the grand total is calculated. I really just want to show the net.

Straight table is NOT the answer, I have multiple dimensions.

Found a solution where you create a assosiative table and somehow use that with aggregated values for dimension and then use the fact table for (pivot)total.

But I can't get it in my head how to create that table. What to use as key?

Really would like to be able to control what goes in the, grand, total with set analysis or something.

Suggestions?

Regards

thomas

[stephencredmond · Accepted solution]

Hi Thomas,

You could using an If statement in your expression using the Dimensionality() function.  I believe that 0 is the grand total:

if(Dimensionality()=0, Null(), Sum(Myfield))

Regards,

Stephen

[#3]

Excellent!

With Null() the total row dissapears but with a set analysis expression it works.

if(Dimensionality()=0, Sum({<Grupp={Net}>}Myfield)), Sum(Myfield))

[#4]

Hmm, one issue is that if you do a selection (lets say Gross) the grand total still calculates according to set analysis.

Maybe I can solve that with another if that eliminate the grand total if any selection is made. The sub total is still there and that should be enaugh.