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.