Multiple Subtotals (rows) in Pivot Table

[GDIAZ22]

I have created a pivot table with information that is compared by year (inner rows). I need the subtotals to give me an average by year as well. Right now the subtotals are adding both years together.


Any help would be greatly appreciated.


Thank you.

 

[Luke M]

Welcome HarleyGirl, and thanks for posting in the introduction thread!


Would you mind posting a snippet of an example of the layout? I'm having a hard time visualizing what exactly you are wanting. If you copy from XL and paste it here directly, I recommend putting a backtick (`) (found right above the tab key) before and after your data so that spacing doesn't get messed up.

 

[GDIAZ22]

Hi Luke


Thanks for responding. I don't know how to copy and paste using the backtick...can I attach my worksheet somehow?

 

[Luke M]

Sure can.

http://chandoo.org/forums/topic/posting-a-sample-workbook

 

[GDIAZ22]

Here goes a shot....

[pre]

Globe Size	60-72			

Description	                Data	                Year    Weekending	                                                        08/31	  09/15  Average
6428 40 lb Loose 	        Avg Return Per Globe	2012	$0.06 	  $.11   $0.11
% of Total Cartons	2012	7.95%	  25.68% 23.09%
Display Ready 40 lb Loose 	Avg Return Per Globe	2011	$0.10 	  $.10   $0.10
2012	$0.10 	  $.10   $0.10
% of Total Cartons	2011	68.21%	  65.50% 68.21%
2012	23.84%	   8.82% 23.84%
Total Avg Return Per Globe			                $0.10 	  $0.10  10.00
Total % of Total Cartons			                100.00%  100.00% 100.00%

So down here at "Total Avg Return Per Globe & Total % of Total Cartons", I'd like to have these broken out by year.  Added together the information is useless.

Thank you.

[/pre]

 

[Luke M]

Hmm, I guess that didn't really help. Getting ready to call it a day, but if no one else comes by, I'd recommend checking out www.contextures.com

If Debra hasn't figured out a way to make a PivotTable do something, then it can't be done.

 

[GDIAZ22]

I'm so sorry, I posted this in the wrong forum...


Here is the file. Please see my notes in red of what I am trying to accomplish. Any help will be greatly appreciated. Thank you all for your time.


http://www.2shared.com/document/LxV_lsII/Sample_Totals.html

 

[Hui]

@HarleyGirl


With the PT selected, Goto the Pivot Table Field List

Put your Row labels in the order Top to Bottom, Year, Sum Values

Remove the Description Field from the Row Labels

Voila

 

[GDIAZ22]

Hui- Awesome, this worked. Thank you very much. I kept trying "IF" calculated fields and everything else under the sun.


I originally wanted the same format as my original post with 2011 and 2012 above/below one another for each description and then the totals (by year as created with your suggestion) at the bottom of the page, but I don't see any way to accomplish that.


Thank you again for your help, all of you have been life-savers. Have a great day!

 

[Luke M]

Glad we could help!