0
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.
Yes, but i'm not sure how to build both the sub total, and the year based grand total ; with no sub-categories, enabling "subtotal year" does nothing (enabling sub-total for category adds a new line with the total of both years on it) – None – 2011-08-08T00:43:56.270
did you try grouping data ? see: http://www.contextures.com/xlpivot07.html and http://www.ozgrid.com/Excel/grouping-pivot-tables.htm
– JMax – 2011-08-08T06:25:59.063The problem is not related to grouping data; if i was to move the year row group to the column set, the row grand total would contain the numbers i want to see; i'm just not sure how to actually to get those grand tota-by-year numbers to show when year isn't the top level group. – None – 2011-08-09T04:28:12.697