问题描述:

I have a Pivot Table in Excel with a Calculated Field but the Grand Total in the Pivot table doesn't seem to work as you would expect.

You would think that the Grand Total for Column Sum of abs value is the Total of the cells in the column....

abs(1) + abs(3) = 4

IE, the should be column

But it seems to be ignoring that column's cells and using the data source column [value] or the Sum of value column ...

abs(1 + -3) = 2

Calculated Field :=

abs value = ABS(value)

pivot table

level Sum of value Sum of abs value should be (not currently part of the pivot table)

a 1 1 1

b -3 3 3

-------------------------------------------- -----

Grand -2 2 4

Total

I found this incredibly old knowledge article: KB211470 Calculated field returns incorrect grand total in Excel. But, it has no work around.

I can't make modifications to the raw data and I'd like a solution that works with-in the Pivot Table. I'm happy to use DAX/PowerPivot as well. (The results are the same there by the way so it seems the cause is in how the Pivot table works.)

Am I doing something wrong?

Thank you.

相关阅读:
Top