Pivot Tables - Subtotals

[reemoe]

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!

 

[XLGibbs]

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]

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]

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]

Gibbs,

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

 

[XLGibbs]

I dunno...you can send me the file if you wish...and I can send it back with the changes made ..