Assuming I have a pivot table such as
Category Year Amount
A 2009 100
A 2010 150
A 2011 120
Total A: 370
B 2009 50
B 2010 60
B 2010 70
Total B: 180
C 2009 100
C 2010 180
C 2011 50
Total C: 320
Grand Total 870
How would I add totals for each year to the pivot table? I don't want to convert to values and manually insert a couple of manually calculated rows, as the table will need to be able to automatically recalculate. (Amount is several different column fields, simplifed for the example)
e.g. the last rows of the above table are:
Total C: 320
Total 2009 250
Total 2010 390
Total 2011 220
Gramd total 870
The results of the cross-tab query are currently being displayed in a pivot table; the problem with the pivot table is that you can only sub-total; Excel only produces the first output.
If "Year" was a column field, then the grand totals for those rows would be the numbers i would be interested in showing. If "Year" was the outer-most field, then the sub-totals would also show the numbers i would want.
