This content has been marked as final.
Show 2 replies

Re: Pivot Table  Incorrect Partial Sum
Jeremiah Kurpat Mar 14, 2014 4:32 PM (in response to Shuyu Wang)This is because it is not summing the rows, but calculating total Item Cost, then multiplying by the total Count. You want the sum of rows, in this you need different formula for total. You'll need aggr and dimensionality like:
if(dimensionality() =1, sum([Item Cost]) * sum(Unit), sum(aggr(sum([Item Cost]) * sum(Unit), Item)))
This is one that works in my example. For yours, maybe:
if(dimensionality() =1, sum(Item Cost) * count(Item), sum(aggr(sum(Item Cost) * count(Item), Item)))
Please find attached for example.
Hope this helps!
EDIT: You should be able to get away with just:
sum(aggr(sum(Item Cost) * count(Item), Item))

Total.qvw 149.5 K


Re: Pivot Table  Incorrect Partial Sum
Massimo Grossi Mar 14, 2014 4:34 PM (in response to Shuyu Wang)sum([Item Cost]*Unit)