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.
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.
Example App.qvw 170.0 K
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?
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.