Pivot tables - %

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I would like to show % of a total on a subtotal field, e.g.
46/37269 = 0.7% - The calculation must be inside the Pivot table, because
it get refreshed all the time with new rows of information.

blue jacket 246 0.10%
Jean 1952 0.82%
Pants 5116 2.15%
shirt 15918 6.68%
Shorts 1115 0.47%
skirt 7872 3.30%
Tops 5050 2.12%
blue Total 37269 15.64%
camel Jean 821 0.34%
Pants 507 0.21%
shirt 993 0.42%
skirt 697 0.29%
camel Total 3018 1.27%
 
You would need to add formulas to your pivot table, but why can't you just
uncheck the other colours except the one you want the percentage for? In the
dropdown for the colours you have check marks, if you uncheck everything but
blue it will give you the percentage


Regards,

Peo Sjoblom
 
You can add columns to your data table, and calculate the total for each
colour, and the total for each item colour. For example, with your data
in cells A1:C23 --

Colour Item Qty
Blue Skirt 2000

In cell D1, type: ColourTotal
In cell E1, type: ItemColourTotal
In cell D2, type: =SUMIF($A$2:$A$23,A2,$C$2:$C$23)
In cell E2: =SUMPRODUCT(($A$2:$A$23=A2)*($B$2:$B$23=B2)*($C$2:$C$23))

Copy the formulas down to row 23

Change the Pivot table source to include the new columns
Refresh the pivot table
Select a cell in the pivot table, and on the PivotTable toolbar,
choose PivotTable>Formulas>Calculated Field
Type a name for the field, e.g.: ItemPct
Type the formula: =ItemColourTotal /ColourTotal
Click OK
Format the field as Percentage
 
Back
Top