Pivot Table Calculations

[Guest]

Hi I currently have the following pivot table which shows rolling 12 month
range of data:

Area 1/1/07 2/1/07 Grand Total
A 100 200 300
B 500 600 1100
Total 600 800 1400

And I want to be able to show a % of Total for the Grand Total Column and %
Total for only the current month reporting column (2/1/07). and update every
month going forward. Next month % total for March instead of February. Can
this be done?

 

[Herbert Seidenberg]

Since PT cannot use the Grand Total to calculate an item,
add two fields that calculate the totals and
add yet another field to calculate the percentage.
The result might look like this:

Sum of Value Date
Area 1/1/07 2/1/07 SumDat
A 100 200 300
B 500 600 1100
SumAB 600 800 1400
PctAB 43% 57% 100%

In your source data, add dummy data for all
the months of the year.
SumAB has this formula:
=A+B
SumDat has this formula:
='1/1/07'+'2/1/07'+'3/1/07'+'4/1/07'+.....+'12/1/07'
If the initial source data does not show all the dates,
it will not allow you to enter this formula.
PctAB has this formula:
=SumAB/(SumAB SumDat)
formatted as Percentage.
In Table Options, uncheck Grand Totals.