# Pivot Tables - Subtotals

#### reemoe

##### New Member
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!

### 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
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
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
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
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
I dunno...you can send me the file if you wish...and I can send it back with the changes made ..

Replies
0
Views
328
Replies
5
Views
155
Replies
0
Views
124
Replies
3
Views
133
Replies
4
Views
69