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
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,020
Messages
5,834,989
Members
430,331
Latest member
Syed Yasir Hannan

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top