Pivot Table - calc. field the answer? If so, how?
Posted by John McTigue on May 16, 2001 8:39 AM
The original question was posted in newsgroup microsoft.public.excel.misc [message ID <0d1101c0d7fd$5c2acb20$98e62ecf@tkmsftngxs05>] with a pivot table similar to that shown here. I noticed the question had gone unanswered and asked for a sample set of data (shown below, and which is the source for the pivot table).
Original table:
Data
Country State/Province Count of Client Sum of Amount
Canada Alberta 4 100000
Ontario 5 350000
Canada Total 9 450000
USA Michigan 5 350000
New York 4 100000
Texas 4 460000
USA Total 13 910000
Original question:
"I want to add one more field (column) to the pivot table that computes the percentage of amount of each provice within the country.
I have created a field and set the option for that field to show the data as % of column. But the pivot shows me the percentage of the amount based on the Grand Total and not on the Country Total.
Is there a way that I can get the percentage based on the Country Total?"
One can view % of column total for State/Province with Country as a page field, but is it possible to get something similar to the following, ie without a page field? I have had no success but would love to know the answer, and thought I would try another set of gurus.
Required table:
Data
Country State/Province Count of Client Sum of Amount %Country
Canada Alberta 4 100000 22.2
Ontario 5 350000 77.8
Canada Total 9 450000 100.0
USA Michigan 5 350000 38.5
New York 4 100000 11.0
Texas 4 460000 50.6
USA Total 13 910000 100.0
Source data:
Country State/Province Amount Client
Canada Alberta 10000 A
Canada Alberta 20000 B
Canada Alberta 30000 C
Canada Alberta 40000 D
Canada Ontario 50000 E
Canada Ontario 60000 F
Canada Ontario 70000 G
Canada Ontario 80000 H
Canada Ontario 90000 I
USA New York 10000 A1
USA New York 20000 A2
USA New York 30000 A3
USA New York 40000 A4
USA Michigan 50000 A5
USA Michigan 60000 A6
USA Michigan 70000 A7
USA Michigan 80000 A8
USA Michigan 90000 A9
USA Texas 100000 A10
USA Texas 110000 A11
USA Texas 120000 A12
USA Texas 130000 A13
Posted by Mark W. on May 16, 2001 5:33 PM
We love beating the competition here!!
John, add a new 'Provincial_Ratio' column to your
data list, just to the right of your existing
'Amount' column (which I'll assume is in column C),
enter the formula, =C2/SUMIF($A$2:$A$23,A2,$C$2:$C$23),
on the 1st data row of this new column, and Copy down.
Refresh your PivotTable. Launch the PivotTable wizard.
Drag the new 'Provincial_Ratio' field to the DATA area.
Double click the 'Provincial_Ratio' field button,
change the contents of its "Name" field to '% of
Country', press the [ Number... ] button, change
the format to Percentage with 1 decimal place,
press [ OK ] on the Format dialog, press [ OK ]
on the PivotTable Field dialog, and press
[ Finish ] to close the PivotTable wizard. You'll
notice that the 'Grand Total' value for this new
PivotTable column is 200.0% so let's hide it.
Choose the PivotTable | Select | Enable Selection
command from the PivotTable toolbar. The little
Enable Selection icon should look "depressed" [like
me after a devastating day at work : ) ]. Right
mouse click the cell at the intersection of
'% of Country' and 'Grand Total', and choose the
Format Cell... command. Click on the Custom
category, enter ;;; in the Type field and press
[ OK ].
Voila!
Posted by Mark W. on May 16, 2001 5:39 PM
Just noticed...
...that you probably had the "Column totals for
columns" option deselected. So you may not see
the 200.0% 'Grand Total' value. But, if you ever
select this option you'll know what to do...
"You'll notice that the 'Grand Total' value for this
new PivotTable column is 200.0% so let's hide it.
Choose the PivotTable | Select | Enable Selection
command from the PivotTable toolbar. The little
Enable Selection icon should look "depressed" [like
me after a devastating day at work : ) ]. Right
mouse click the cell at the intersection of
'% of Country' and 'Grand Total', and choose the
Format Cell... command. Click on the Custom
category, enter ;;; in the Type field and press
[ OK ]." John, add a new 'Provincial_Ratio' column to your
: The original question was posted in newsgroup microsoft.public.excel.misc [message ID <0d1101c0d7fd$5c2acb20$98e62ecf@tkmsftngxs05>] with a pivot table similar to that shown here. I noticed the question had gone unanswered and asked for a sample set of data (shown below, and which is the source for the pivot table). : Original table
Posted by John McTigue on May 16, 2001 9:03 PM
Re: Just noticed...
Mark,
Thank you, that is spot on.
I couldn't see the wood for the trees. In fact, even with a chain saw I think I still would not have seen it.
BTW I admit to targeting my post after reading the archives. You seem to have answered many previous PT questions. I just waited until I saw you were online... :-)
Regards,
John McTigue
