The show total function in Pivot table is not correct

[#1]

Hi All,

I have one table:

load *

Inline

[Category,Revenue

'A',0

'B',100

'C',200

];

And I use one pivot table to show the revenue. However, as you can see, there is no revenue for A category, so I assigned the revenue from B to A with the expression by  if(Category='A',sum({<Category={'B'}>}total  Revenue),sum(Revenue))

It works well, but the total number is not right, the total number should be 400 not 300.

I have attached the QVW, could you help me out?

Thanks.

[Anonymous · Accepted solution]

Heya Isaac,

The value on the Total row will be calculated just like for the other dimension values just that the Category value is null for this row. This means that the Category <> A and the total will only calculate the sum(Revenue) part which is 300.

You can use aggr() to create the "sum of rows" functionality for a pivot table that you're looking for.

Try this:

sum(aggr(if(Category='A',sum({<Category={'B'}>}total  Revenue),sum(Revenue)), Category))

[#3]

Thanks fore your correct answer, I am Isaac Li.

[Anonymous]

I know buddy. Say hi to Stanley and good luck with the development!

Cheers,

Johannes