OK thought I'd take a try at this. First off mixing Box data and Location data in the same pivot table results in an overly complex table that doesn't give clear information for location or box contents. I'd suggest using 2 pivot tables one to show and calculate the Location data and one to show and calculate the Box data. I've attached an example zip file see the 2 pivot tables below your original sample.
The 'Count of Toys in box' Column in your data is not needed and not used in the pivot table as the count of toys can be calculated from the count of Box IDs. Each Toy has a box identifier.
Also be aware of the calculation function. You want to * count * boxes (box ids) and * sum * toy values. When you set up the pivot table it will be necessary to select the data field and set the calc function for each of the fields Boxes and Value.
The Formulas can not be made relative to the cells in the Pivot table. At least I can't see a way of making them relative. This means each time you add data or recreate the pivot table you'll need to set each formula for every row calculation. You can however refer to the cells in your Pivot table. (not sure if you realized this) So the formulas will be like this:
Code:
Total Value / # of Boxes (for each location)
=GETPIVOTDATA("Sum of Value",$A$30,"Location","US")/GETPIVOTDATA("Count of Box",$A$30,"Location","US")
# of Toys / Total Value
=GETPIVOTDATA("Count of Box",$A$40,"Box",1)/GETPIVOTDATA("Sum of Value",$A$40,"Box",1)
dBforums
>
Calculated Fields in Pivot Table
12-29-05, 10:51
Linear Mode
