display data as a percentage of a subtotal in excel pivot table

[Guest]

how can data be displayed as a percentage of a subtotal in a pivot table?
for example, I can display salesperson A's 1st quarter sales as a percentage
of yearly sales. What I want to do is display salesperson A's 1st quarter
sales as a percentage of quarter 1 total sales.

 

[Debra Dalgleish]

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

Salesperson Date Sales
Sam 1-Jan-05 200

In cell D1, type: Qtr
In cell D2, type: =CHOOSE(MONTH(B2),1,1,1,2,2,2,3,3,3,4,4,4)

In cell E1, type: PctQtr
In cell E2, type: =I2/SUMIF($C$2:$C$200,C2,$I$2:$I$200)

Copy the formulas down to row 200

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

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

 

[Dave Peterson]

I like this way to indicate quarters:

=YEAR(A2)&"-Qtr"&INT((MONTH(A2)+2)/3)

It evaluates to something like: 2005-Qtr1
(sometimes the year is useful.)