Incorrect Total Sum in Pivot Table

[#1]

Hello,

I used to sum calculated negative results in a Pivot Table and the grand total at the end of the table is incorrect. Once I switch to straight table and set the properties to summarize "of rows", I'm fine. Unfortunately this setting is not available in my Pivot Table.

I used to try it with set analysis, but I don't think this might help.

Example:

1) I have different calculated results of value 'x' as starting situation ->   sum ( x )

   sum ( x ) returns positive and negative Values.

2) I want to sum only negative results of 'sum( x )' so I tried the simple way

  'if (sum( x ) < 0, sum ( x ), 0)'

  The results of the rows in my table are correct, BUT the grand total is incorrect. The grand total is the sum of all results (negative + positive). It doesn't matter if I try to use additional aggregation in the formula

All other solution approaches didn't help so far. There might be a very simple solution... Thanks for any hint!

Bo

[Gysbert_Wassenaar]

Try putting a sum(aggr( ...expression... , DimA, DimX)) around your expression where DimA, DimX are the dimensions of your pivot table.

And you can use rangemax(sum(x),0) instead of the if statement.

[#3]

Hello Gysbert,

thanks for your reply! I think you mean 'rangemin', because I want to sum all negative results...

I already tried to do it with aggregation. Now the formula is

=RangeMin (sum (aggr(sum({$<Finanzkonto = {"*"}>} Saldo), Mandant, Kontonamelang)),0)

and the result is still the same... grand total is incorrect, s. below.

Frustrating...

[Gysbert_Wassenaar · Accepted solution]

I meant something like this:

sum (aggr(rangemin(sum({$<Finanzkonto = {"*"}>} Saldo),0), Mandant, Kontonamelang))

If it doesn't work can you post an example document that demonstrates the problem?

[#5]

That's it! I have to spend more time with range-functions...  Great! Thank you very much!!

[#6]

Hey guys, 

I am given a task to calculate the frequency of calls across a territory. If the rep called a physician regarding the sale of the product 5 times, then frequency is 5 and HCP count is 1....I generated frequencies from 1 to 124 in my pivot table using a calculated dimension which is working fine. But my concern is :

My manager wants frequencies till 19 in order from 1..2..3..4...5..6.....19...

And from the frequency 21-124 as 20+.

I would be grateful if someone helps me with this.....Eager for the reply....

[stefano27]

Thank you. The solution above with the RangeMin worked for me as well.

[stefano27]

Thank you. This worked for me as well.