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

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.

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.


Fl pivot user wrote:
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
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

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.)

Debra Dalgleish wrote:

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.

Fl pivot user wrote:
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
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

--

Dave Peterson