Calculated field for Grand Total

[jomili]

Okay, I can easily do this outside of the pivot, but I'm really interested in learning how to do this through a calculated field. In the attachment I have a pivot. In my columns I have EA Elig, which has two value fields, EA and Non-EA. What I'd like is a calculated field showing EA/SUM(EA+Non-EA). When I try to do it as a calculated field "EA Elig" shows up as a field I can use, but "EA" and "Non-EA" don't. Any help would be greatly appreciated.

[stasinek]

Hello,
I changed some of Your data layout. See if this solves Your problem

[xlnitwit]

Hi,

What you describe is a calculated item rather than a calculated field, for which the formula
= EA/( EA+ 'Non-EA')
will provide the answer. However, adding a calculated item will have some probably undesired side effects as it will be calculated for all available rows, not merely those which have data, and you will therefore see every item combination in your pivot table.

[jomili]

Stasinek,
Apparently we think alike. Yesterday after my post I came to the same solution, but hoped that somebody could come up with a solution that didn't require the data modification.

XlNitWit,
Thanks for helping to clarify the problem. Yep, I tried it as a Calculated Item, and it sure didn't work like I wanted.

[stasinek]

I see that you have version 2010, so you can leave the data layout unchanged. However, you should use the Power Query and so make changes. The data table after the change should be used to create a PivotTable

[jomili]

Unfortunately we're locked down so I can't load Power Query. However, we're just about to upgrade to 2016, so I'll have it then (and a whole new learning curve to tackle).