Grand subtotal by pivot item 2

[pivotlover]

I have a pivot table like the following
----------------Qtr 1 ------- Qtr2 ------- Total
------------- CA AZ TX --- CA AZ TX ----
Sales -------- 10 15 11 --- 5 10 9 ----- 50

I wonder if Pivot table can be set up to total by State (without changing the order of pivot items. ie. keep Qtr as the 1st pivot item, and state as the second), like

----------------Qtr 1 ------- Qtr2 ------- Total
------------- CA AZ TX --- CA AZ TX ---- CA AZ TX
Sales -------- 10 15 11 --- 5 10 9 -----15 25 10

I can't use the Calculate Field item due to the large dataset size.

Please help!!!

 

[GlennUK]

Why don't you just have another Pivottable to total by state.

 

[GlennUK]

Actually, I think if you just changed the field settings for state to have a Custom subtotal of Sum, it will do exactly what you want.

 

[pivotlover]

Glenn, Thanks for taking the time to reply to my question.
Changing the subtotal to Custom/Sum actually doesn't give me total for Q1+Q2 by state.

I guess the pivot table can't do what I want.

Thanks anyway.

 

[GlennUK]

Glenn, Thanks for taking the time to reply to my question.
Changing the subtotal to Custom/Sum actually doesn't give me total for Q1+Q2 by state.

I guess the pivot table can't do what I want.

Thanks anyway.

It works for me on the test data that I knocked up. What steps did you take to try to alter the subtotal setting exactly?

 

[pivotlover]

I double clicked on the 'State' column header and, in the pivot table field window that pops up, selected Custom and Sum. That gave me two columns (one for data, another - exact same number - for the total) for each state WITHIN A QUARTER.

Am I doing it wrong?

Thanks for your help.

 

[GlennUK]

Without seeing your source data, and the actual pivottable I can't guess what you are doing wrong. It works for me ... I must have just the right data layout for the pivottable to work that way ( maybe?!?! ).