Using '% of Total' in calculated field in Pivot Table

[goofy11]

I have a Pivot Table with Inventory, % of Inventory, Sales, and % of Sales.
To get '% of Inventory' and '% of Sales' I used the Inventory and Sales
fields a second time and used the '% of Column' option. My table looks
something like this.

Category Inventory % of Inventory Sales %
of Sales
aaaa $30,000 11.91% $31,000
12.31%
bbbb $20,000 8.32% $22,000
9.29%
cccc $15,000 6.38% $14,000
5.25%
etc.

What I need to do is subtract '% of Inventory' from '% of Sales'. I realize
I can enter this calculation outside of the Pivot Table, but I would love to
have it be part of the Pivot Table because I plan to drill-down/expand the
Pivot Table and would like it to stay dynamic. Is this possible?

Jeff

 

[carlyman]

On the toolbar PivotTable menu (doesn't work if you right-click), choos
Formula and then Calculated Field or Calculated Item; both of thes
options allow for dynamic entries. I forget which one you'll need t
use in this case...but it'll give you a start until someone else ca
respond.

-J

 

[goofy11]

I've had experience inserting calculated fields, however, I don't see how to
do it based on a '% of Total' field. I'm not even sure it's possible. But
if it is, I would love it. To clarify, the new column I'm trying to insert
would be the 'Difference' column listed below (% of Sales - % of Inventory).

Category Inventory % of Inventory Sales %
of Sales Difference
aaaa $30,000 11.91% $31,000
12.31% 0.4%
bbbb $20,000 8.32% $22,000
9.29% 0.97%
cccc $15,000 6.38% $14,000
5.25% -1.13%
etc.