Pivot table subtotal percentages ???

jdowski

Board Regular
Joined
Apr 21, 2002
Messages
235
Hi All,

I have been trying to come up to speed on Pivot Tables lately. I have alot more recent experience in writing Access reports and I was trying to recreate some of those reports with Pivot Tables.

My question is this, if I have a Pivot Table (PT) and my row fields are City & State, how can I figure a citie's total as a percentage of it's state ? I can put in percentages but they figure each row fields percentage of the Grand Total. Ideally I'd like to see each cities % of ST and each ST's % of the Grand Total.

Is this a "hiccup" of PTs ?? I thought I might have caught a remark about this on the web but this is the first time I've spent the time to post about it...

Thanks very much !!!! :LOL:

Joe in Oxford, CT
 

Some videos you may like

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

shades

Well-known Member
Joined
Mar 20, 2002
Messages
1,550
Howdy. Can you put State as columns, then the percentage for each city will be according to state?
 

jdowski

Board Regular
Joined
Apr 21, 2002
Messages
235
Well I left out a time dimension as my column value because it wasn't pertinent to my immediate question regarding subtotal percentages. Besides, wouldn't using state as a column heading preclude the cities from rolling up to the state level ???
 

shades

Well-known Member
Joined
Mar 20, 2002
Messages
1,550

ADVERTISEMENT

As a column, the State would roll up all cities that are assocaited with it.

But, it might have to do with how you have arranged the underlying datatable. And, what about the time element?
 

jdowski

Board Regular
Joined
Apr 21, 2002
Messages
235
You lost me... :eek:

Number one I don't want to spread all the states out as a column heading because the report will get awfully wide....I played with doing it that way though and though I could get the towns to roll up to the states as a percentage, I then couldn't get the states to roll up to the grand total as a percentage.....
Am I missing something ?? :unsure:
 

shades

Well-known Member
Joined
Mar 20, 2002
Messages
1,550
Perhaps one way around this (if it is an option for you) is to include a State total column for each line in your underlying datatable (use SUMIF). Then you could use that as a base to determine % of in the dropdown when you set up the data in the PivotTable.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,674
Messages
5,573,598
Members
412,537
Latest member
Mohamed_5966
Top