Calculated Field in Pivot Table Based On Totals-and hide a data fi

[Leo Demarce]

I have a pivot table that looks something like this:

Customer CategoryA CategoryB TotalSale
TotalWeight
NetSale ExtWeight NetSale ExtWeight

ABC 1000 100 2000 100 3000
200

What I would ideally like to do is 2 things:
1, hide the ExtWeight column but still show the TotalWeight on the right
2, i want to create a calculated field on the right of the TotalWeight that
would be the average of the totalsale to totalweight (totalsale/totalweight)
which would be the avg sale/weight.

Any suggestions

 

[Shane Devenshire]

Regarding the second of these - Within the pivot table you can't have a field
to the right of the grand total column, if thats what the Total Sales column
is.

Regarding the other one if you want you can send me a sample file and I will
try to see what I can do. My name, no spaces @SBCGlobal.Net

 

[Roger Govier]

Hi

If you omit ext weight from PT, then you won't get Total Weight.
You could just Hide those columns totally on the Sheet, so it didn't
display.

As for the second part you need to use the GetPivotData function.
Using direct cell references like =F5/G5 copied down, will work, but they
are likely to become incorrect when the PT is refreshed.

For help on how to use the GetPivotdata function, take a look at
http://www.contextures.com/xlPivot06.html

 

[Herbert Seidenberg]

Excel 2007
Pivot Table,
Auxiliary Table with GetPivotData() resizes
dynamically with new data or refresh.
Shown Share file here:
http://www.mediafire.com/file/umot0atjmjy/02_06_09.xlsm