5 Replies Latest reply: Mar 31, 2010 6:37 AM by jbotha

# Total sum in Pivot

Hi, I've been reading the forum all night long, now I'm tired so maybe someone can help me out with this pivot table. I can't get the total sum to calculate right. The sum should be 304065.

I Use following code to do the calculation. I tried to use the aggr function but I get the same result :( I would be more than happy if someone could give me a hint. Thanks!

=IF([Curr]>'',SUM([Curr]*[Unddel_ordervalue]),Sum([Unddel_ordervalue]))

#### Sum(If([Curr] >"", [Curr]*[Unddel_ordervalue],[Unddel_ordervalue]))

-Ed

• ###### Total sum in Pivot

Unfortunatly, that was not the solution, I think it has to be someting with the aggr function....

Well the result from your solution (depending if I use '' or "":

Thanks anyway!

• ###### Total sum in Pivot

Hi danne31,

There is a chapter on this in the reference manual. "Sum of rows in a pivot table" or something like that in the back somewhere.

The if statement in the expression can make the dimension independent total be something else than the sum of rows. Still correct, but not what one would like to see. In that case you can aggregate the sums for each dimension value and then sum these up for the total.

if the original expression was: =IF([Curr]>'',SUM([Curr]*[Unddel_ordervalue]),Sum([Unddel_ordervalue]))

You should be able to write: =sum(aggr(IF([Curr]>'',SUM([Curr]*[Unddel_ordervalue]),Sum([Unddel_ordervalue])), Kund_beskrivning))

However, I see that you have more than one dimension hidden under the pivot expansion icons so you might want to add these dimension fields to the aggr function. This is all better explained in the reference manual so I suggest you look up the chapter I'm referring to.

cheers,

Johannes

• ###### Total sum in Pivot

Hi Johannes!

Perfect! Thanks a lot, it works just the way i want to!

/Daniel

• ###### Total sum in Pivot

Thank you Johannes

I have been pulling my hair out over this for almost 10 hours straight.