Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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
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
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
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
Hi Razor,
Even I faced such type of problem in my Pivot.... You can get perfect subtotal if you use :
=Sum(Aggr(.....Put your entire Expression here.....))
If still you didnt get correct total then let me know...
Regards,
Yojas