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

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
Howdy. Can you put State as columns, then the percentage for each city will be according to state?
 
Upvote 0
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 ???
 
Upvote 0
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?
 
Upvote 0
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:
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,236
Members
448,555
Latest member
RobertJones1986

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
Back
Top