Pivot Tables - Subtotals

reemoe

New Member
Joined
Oct 24, 2005
Messages
41
Can pivot tables contain two separately calculated Subtotals? For example, have a subtotal for Cities that uses one calculated field, and have a subtotal for State that uses another calculated field (so as to keep the State subtotal from being the total of the City subtotals)?

Thanks for the help!
 

Some videos you may like

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

XLGibbs

Well-known Member
Joined
Feb 25, 2005
Messages
2,446
Depending on what you are measuring you can have both fields either as row or column fields, and designate the subtotal by double clicking on the feild and selecting Automatic totals for the heading. You can also customize the subtotal function.

Additionally, if both these fields are in your displayed table, you can set the table to show totals by columns, rows , neither or both.
 

reemoe

New Member
Joined
Oct 24, 2005
Messages
41
I'm not sure that answers my question. I have about 12 Cities that average about 3% each, but my State subtotal is showing 36% (but it should be near 3% like the cities).
 

XLGibbs

Well-known Member
Joined
Feb 25, 2005
Messages
2,446
You can customize the subtotals and display them as an average as opposed to a total.

36% is obviously the sum which is the default Subtotal. double click the field and select customize for the subtotals. You should be able to set it to average as opposed to Sum. I have done it many times.
 

reemoe

New Member
Joined
Oct 24, 2005
Messages
41
Gibbs,

When changing it to Average, Excel took out the 36% altogether (it now displays a blank cell!). What might cause this??
 

XLGibbs

Well-known Member
Joined
Feb 25, 2005
Messages
2,446
I dunno...you can send me the file if you wish...and I can send it back with the changes made ..
 

Watch MrExcel Video

Forum statistics

Threads
1,119,139
Messages
5,576,306
Members
412,716
Latest member
thviid
Top