Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using more than one subtotal with same data set | Excel Worksheet Functions | |||
Need Formula to display pivot table source data | Excel Worksheet Functions | |||
Pulling data from 1 sheet to another | Excel Worksheet Functions | |||
How do I display a data table in an Excel 2002 chart? | Charts and Charting in Excel | |||
Data Filter - Not all rows in spreadsheet will display in Autofilt | Excel Worksheet Functions |