7 Replies Latest reply: Sep 30, 2013 9:26 AM by Yojas Samarth

# Pivot Table Column Total & Subtotal Problem

Hi guys,

Having a pretty strange problem here. I have a pivot table with different expressions.

The expressions are as simple as sum(value), sum(price) and so on.

One of my expressions has a division, like sum(value * price)/sum(quantity).

For every other expression in my pivot table, the totals and sub totals are correct, but for this expression, the totals and sub totals are wrong.

Can someone help me with this please?

Thanks

• ###### Pivot Table Column Total & Subtotal Problem

Hi,

the pivot table totals are always calculated as expression total (like putting the expression in a text box).

(for more details see also the chapter "nested aggregations and related issues" - "sum of rows in pivot tables" in the Help file)

To see what the exact problem in your case is, it would be best if you could post an example file demonstrating your issue.

Reagrds,

Stefan

• ###### Re: Pivot Table Column Total & Subtotal Problem

Hi,

I have attached a sample app to explain my problem.

My subtotals and overall total in the pivot table are wrong, as the pivot table for some reason does not sum the total and sub total values correctly.

Because I am using two groups, I tried using the aggr function, but couldn't get it to work properly.

Please I need help to get my total and sub total values right.

Thanks

• ###### Re: Pivot Table Column Total & Subtotal Problem

Hmm,

if I look at your pivot with dimensions Country and Product Type I see:

 Country Product Type Value Weighted Value Count Canada Furniture £140.295 £731 192 730,7031 Canada Office £155.745 £811 192 811,1719 Canada Total £296.040 £771 384 770,9375 Mexico Furniture £93.427 £487 192 486,599 Mexico Office £106.639 £555 192 555,4115 Mexico Total £200.066 £521 384 521,0052 U.S.A. Furniture £457.179 £794 576 793,7129 U.S.A. Office £451.422 £784 576 783,718 U.S.A. Total £908.600 £789 1152 788,7155 Total £1.404.706 £732 1920 731,6178

(I 've added last two columns to show count and average value calculated in Excel.

The Weighted Value seems ok to me, also in subtotals and total. For a given segment, the subtotal Wighted Value is of course not the average of the Weighted Values of the single rows.

Could you give an example of what you expect to see?

Regards,

Stefan

• ###### Re: Pivot Table Column Total & Subtotal Problem

Hi Stefan,

Thanks for your response. In the total line for say Canada. I don't want the total for the Weighted Value to be the average of 731 and 811. I want it to be a sum. I want it to just display the sum of (731+811). Just the same way the value column adds the values together for the total row.

I hope I have made myself clear enough.

Thank you

• ###### Re: Pivot Table Column Total & Subtotal Problem

Ok,

I think this should work as expression:

=SUM(aggr(avg(Actual),[New Group],[New Group2]))

Just for my curiosity: how do you interprete/use the sum of average or weighted values?

Regards,

Stefan

edited by swuehl

• ###### Re: Pivot Table Column Total & Subtotal Problem

Thanks for all your help Stefan.

I don't really like weighted values as well, but my boss would like to use them in this application to determine the relative importance of each quantity on the average.

Kind Regards

• ###### Re: Pivot Table Column Total & Subtotal Problem

Hi Razor,

Even I faced such type of problem in my Pivot.... You can get perfect subtotal if you use  :