Can calculated fields reference grand totals in formula, in pivot tables?

1
votes

I want to reference grand totals in a formula I want to use in pivot table. In essence I want to use %of column total in a calculated field, but I can't do that, so I thought I might be able to reference to the column grand total and I still haven't found a solution for it. I want to do this inside the pivot table not outside.

1
excelpivotpivot-table
it would be something like = (1-'Water Use'/'Total Water Use')/(1-'Emissions'/'Total Emissions') – funSizes

1 Answers

0
votes

I don't think that you can do it with traditional pivot tables. Such calculated fields are - depending of the calculation - not so reliable.

However, if you have the right MS Office Version, you could use the Power Pivot add-in with its data model to do it. The capabilities of this new powerful feature are awful. However you have to learn the technique, but the output will bring you to new horizonts for data handling with Excel. Give it a try.