Total sum in Pivot

[#1]

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]))

[#2]

Try changing your calculation:

from this:

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

To this:

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

-Ed

[#3]

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!

[Anonymous · Accepted solution]

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

[#5]

Hi Johannes!

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

/Daniel

[#6]

Thank you Johannes

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