How can I Show data as a % of Sub Group in a Pivot Table

[Faulk]

I want to show a column of data (% of City) as a % of a sub group
total. Here's my example of how the pivot table should look:

State City Carrier Sq Yards % of City (Sq Yards2)
Alabama
Birmingham
Carrier 1 10 20%
Carrier 2 20 40%
Carrier 3 10 20%
Carrier 4 10 20%
Birmingham Total 50 100%
Mobile
Carrier 1 10 25%
Carrier 2 10 25%
Carrier 3 10 25%
Carrier 4 10 25%
Mobile Total 40 100%

I can't get the last column to calculate. The last column(Sq Yards2)
is simply a duplicate of Sq Yards, however, it is displayed as a % of
column. I don't want Column to show, I want a % of City shown. How
can I do this in a pivot table ??

 

[Debra Dalgleish]

You can add columns to your data table, and calculate the total for each
state, and the percent of state total for each row. For example, with
your data in cells A1200 --

State City Carrier Sq Yards
Alabama Birmingham Carrier 1 10

In cell E1, type: PctCity
In cell E2, type: =D2/SUMIF($B$2:$B$44,B2,$D$2:$D$44)

Copy the formulas down to row 200

Change the Pivot table source to include the new column
Refresh the pivot table
Add the PctCity to the Data area
Format the field as Percentage

Note: State total and Grand Total will show an incorrect amount in this
column.

 

[Faulk]

Your Awesome!!!! That worked. Thanks for the help!

 

[Debra Dalgleish]

You're welcome! Thank you for telling me that it worked.

Your Awesome!!!! That worked. Thanks for the help!

 

[sgjones73]

hey you can do it as part of the pivot table if you wish
by adding the value field to the pivot table again and double clicking
on it, you then have a host of options on how to calculate the data