Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivottable: Show value as a percent of Subtotal
I need to get my pivot table to return a percent of subtotal instead of a
percent of column. Here's what I want: Region State Sales Percent of Sales North Montana 10 10 % Idaho 90 90 % North Total 100 100 % West Calif 50 25 % Oregon 100 50 % Arizona 50 25 % West Total 200 100% I can easily do this using the Show Value As % of Column option with field settings for the Sales Field, but as the description implies this gives me just the percent of of the grand total for the column rather than for the subtotals. Is it possible to do what I want here? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivottable: Show value as a percent of Subtotal
The best I can figure is to make Region the Page field. Then, when you select
each region, it should give % of that region's total. HTH "Ted M H" wrote: I need to get my pivot table to return a percent of subtotal instead of a percent of column. Here's what I want: Region State Sales Percent of Sales North Montana 10 10 % Idaho 90 90 % North Total 100 100 % West Calif 50 25 % Oregon 100 50 % Arizona 50 25 % West Total 200 100% I can easily do this using the Show Value As % of Column option with field settings for the Sales Field, but as the description implies this gives me just the percent of of the grand total for the column rather than for the subtotals. Is it possible to do what I want here? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivottable: Show value as a percent of Subtotal
This is a hard one for just pivot tables.
I would do the total by region in one pivot table then for the pivot table below create two columns 1) =IF(A3="",E2,A3) this will copy down the region from the pivot tables 2) =C3/VLOOKUP(E3,$F$3:$G$4,2) This will look at the number in the sales column (c3) and divide it by looking at your table from F3:G4 and finding out the total for that region F g North 100 West 200 "Ted M H" wrote: I need to get my pivot table to return a percent of subtotal instead of a percent of column. Here's what I want: Region State Sales Percent of Sales North Montana 10 10 % Idaho 90 90 % North Total 100 100 % West Calif 50 25 % Oregon 100 50 % Arizona 50 25 % West Total 200 100% I can easily do this using the Show Value As % of Column option with field settings for the Sales Field, but as the description implies this gives me just the percent of of the grand total for the column rather than for the subtotals. Is it possible to do what I want here? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivottable: Show value as a percent of Subtotal
Hi Don,
Clever solution--thanks for your effort. It works great as long as I don't mess around with filters or hide / show options in the pivot table, or refresh the pivot table with additional source data, etc. "Don" wrote: This is a hard one for just pivot tables. I would do the total by region in one pivot table then for the pivot table below create two columns 1) =IF(A3="",E2,A3) this will copy down the region from the pivot tables 2) =C3/VLOOKUP(E3,$F$3:$G$4,2) This will look at the number in the sales column (c3) and divide it by looking at your table from F3:G4 and finding out the total for that region F g North 100 West 200 "Ted M H" wrote: I need to get my pivot table to return a percent of subtotal instead of a percent of column. Here's what I want: Region State Sales Percent of Sales North Montana 10 10 % Idaho 90 90 % North Total 100 100 % West Calif 50 25 % Oregon 100 50 % Arizona 50 25 % West Total 200 100% I can easily do this using the Show Value As % of Column option with field settings for the Sales Field, but as the description implies this gives me just the percent of of the grand total for the column rather than for the subtotals. Is it possible to do what I want here? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivottable: Show value as a percent of Subtotal
Don,
I'm trying to solve the same problem as Ted M H, I think, but not sure what the "base" pivot table looks like so I can apply your recommended cell formulas correctly. If either of you recall, would you post something more descriptive? Thanks, Jim "Don" wrote: This is a hard one for just pivot tables. I would do the total by region in one pivot table then for the pivot table below create two columns 1) =IF(A3="",E2,A3) this will copy down the region from the pivot tables 2) =C3/VLOOKUP(E3,$F$3:$G$4,2) This will look at the number in the sales column (c3) and divide it by looking at your table from F3:G4 and finding out the total for that region F g North 100 West 200 "Ted M H" wrote: I need to get my pivot table to return a percent of subtotal instead of a percent of column. Here's what I want: Region State Sales Percent of Sales North Montana 10 10 % Idaho 90 90 % North Total 100 100 % West Calif 50 25 % Oregon 100 50 % Arizona 50 25 % West Total 200 100% I can easily do this using the Show Value As % of Column option with field settings for the Sales Field, but as the description implies this gives me just the percent of of the grand total for the column rather than for the subtotals. Is it possible to do what I want here? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivottable: Show value as a percent of Subtotal
Hi Jim,
Not certain that I understand your question...If you're asking what the original problem description was, it's at the bottom of this post. Columns A, B, and C contain the pivot table with fields Region (A) State (B) and Sales (C). Region and State are both Row area fields and Sales is the Values area field. Column D is the problem: How to get each State's percent of its Region sales. For example: Idaho is 90 percent of the North Region's sales. By the way, I came up with a nice solution to this problem, building on others' ideas. Here's the forumla entered in cell D5 and then just copied down in Column D to the last row of the pivot table in columns A:C. =C5/INDEX(A5:INDIRECT("C"&COUNTA(C:C)+3),MATCH("* total",A5:INDIRECT("A"&COUNTA(C:C)+3),0),3) For this to work you should have the Pivot table arranged in Tabular form and the first row item should be in row 5 (column headings, page fields, etc. in rows 1 - 4). Also, don't override the default Subtotal names since the formula looks for the literals total to find the subtotal rows. And be sure to put the formula in col D before you do any filtering in the pivot table. If you make any big changes to the pivot table, just reenter the formula and copy it down again. "uslanja" wrote: Don, I'm trying to solve the same problem as Ted M H, I think, but not sure what the "base" pivot table looks like so I can apply your recommended cell formulas correctly. If either of you recall, would you post something more descriptive? Thanks, Jim "Don" wrote: This is a hard one for just pivot tables. I would do the total by region in one pivot table then for the pivot table below create two columns 1) =IF(A3="",E2,A3) this will copy down the region from the pivot tables 2) =C3/VLOOKUP(E3,$F$3:$G$4,2) This will look at the number in the sales column (c3) and divide it by looking at your table from F3:G4 and finding out the total for that region F g North 100 West 200 "Ted M H" wrote: I need to get my pivot table to return a percent of subtotal instead of a percent of column. Here's what I want: Region State Sales Percent of Sales North Montana 10 10 % Idaho 90 90 % North Total 100 100 % West Calif 50 25 % Oregon 100 50 % Arizona 50 25 % West Total 200 100% I can easily do this using the Show Value As % of Column option with field settings for the Sales Field, but as the description implies this gives me just the percent of of the grand total for the column rather than for the subtotals. Is it possible to do what I want here? |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivottable: Show value as a percent of Subtotal
Works just like you said, Ted, and just what I needed.
Very nice formula. Thanks very much. /Jim "Ted M H" wrote: Hi Jim, Not certain that I understand your question...If you're asking what the original problem description was, it's at the bottom of this post. Columns A, B, and C contain the pivot table with fields Region (A) State (B) and Sales (C). Region and State are both Row area fields and Sales is the Values area field. Column D is the problem: How to get each State's percent of its Region sales. For example: Idaho is 90 percent of the North Region's sales. By the way, I came up with a nice solution to this problem, building on others' ideas. Here's the forumla entered in cell D5 and then just copied down in Column D to the last row of the pivot table in columns A:C. =C5/INDEX(A5:INDIRECT("C"&COUNTA(C:C)+3),MATCH("* total",A5:INDIRECT("A"&COUNTA(C:C)+3),0),3) For this to work you should have the Pivot table arranged in Tabular form and the first row item should be in row 5 (column headings, page fields, etc. in rows 1 - 4). Also, don't override the default Subtotal names since the formula looks for the literals total to find the subtotal rows. And be sure to put the formula in col D before you do any filtering in the pivot table. If you make any big changes to the pivot table, just reenter the formula and copy it down again. "uslanja" wrote: Don, I'm trying to solve the same problem as Ted M H, I think, but not sure what the "base" pivot table looks like so I can apply your recommended cell formulas correctly. If either of you recall, would you post something more descriptive? Thanks, Jim "Don" wrote: This is a hard one for just pivot tables. I would do the total by region in one pivot table then for the pivot table below create two columns 1) =IF(A3="",E2,A3) this will copy down the region from the pivot tables 2) =C3/VLOOKUP(E3,$F$3:$G$4,2) This will look at the number in the sales column (c3) and divide it by looking at your table from F3:G4 and finding out the total for that region F g North 100 West 200 "Ted M H" wrote: I need to get my pivot table to return a percent of subtotal instead of a percent of column. Here's what I want: Region State Sales Percent of Sales North Montana 10 10 % Idaho 90 90 % North Total 100 100 % West Calif 50 25 % Oregon 100 50 % Arizona 50 25 % West Total 200 100% I can easily do this using the Show Value As % of Column option with field settings for the Sales Field, but as the description implies this gives me just the percent of of the grand total for the column rather than for the subtotals. Is it possible to do what I want here? |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivottable: Show value as a percent of Subtotal
FYI, I cannot seem to post your reply as an "answer" (because it is).
Perhaps that must come from the original poster? Best, Jim "Ted M H" wrote: Hi Jim, Not certain that I understand your question...If you're asking what the original problem description was, it's at the bottom of this post. Columns A, B, and C contain the pivot table with fields Region (A) State (B) and Sales (C). Region and State are both Row area fields and Sales is the Values area field. Column D is the problem: How to get each State's percent of its Region sales. For example: Idaho is 90 percent of the North Region's sales. By the way, I came up with a nice solution to this problem, building on others' ideas. Here's the forumla entered in cell D5 and then just copied down in Column D to the last row of the pivot table in columns A:C. =C5/INDEX(A5:INDIRECT("C"&COUNTA(C:C)+3),MATCH("* total",A5:INDIRECT("A"&COUNTA(C:C)+3),0),3) For this to work you should have the Pivot table arranged in Tabular form and the first row item should be in row 5 (column headings, page fields, etc. in rows 1 - 4). Also, don't override the default Subtotal names since the formula looks for the literals total to find the subtotal rows. And be sure to put the formula in col D before you do any filtering in the pivot table. If you make any big changes to the pivot table, just reenter the formula and copy it down again. "uslanja" wrote: Don, I'm trying to solve the same problem as Ted M H, I think, but not sure what the "base" pivot table looks like so I can apply your recommended cell formulas correctly. If either of you recall, would you post something more descriptive? Thanks, Jim "Don" wrote: This is a hard one for just pivot tables. I would do the total by region in one pivot table then for the pivot table below create two columns 1) =IF(A3="",E2,A3) this will copy down the region from the pivot tables 2) =C3/VLOOKUP(E3,$F$3:$G$4,2) This will look at the number in the sales column (c3) and divide it by looking at your table from F3:G4 and finding out the total for that region F g North 100 West 200 "Ted M H" wrote: I need to get my pivot table to return a percent of subtotal instead of a percent of column. Here's what I want: Region State Sales Percent of Sales North Montana 10 10 % Idaho 90 90 % North Total 100 100 % West Calif 50 25 % Oregon 100 50 % Arizona 50 25 % West Total 200 100% I can easily do this using the Show Value As % of Column option with field settings for the Sales Field, but as the description implies this gives me just the percent of of the grand total for the column rather than for the subtotals. Is it possible to do what I want here? |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivottable: Show value as a percent of Subtotal
On Apr 11, 3:37 pm, uslanja wrote:
FYI, I cannot seem to post your reply as an "answer" (because it is). Perhaps that must come from the original poster? Best, Jim "Ted M H" wrote: Hi Jim, Not certain that I understand your question...If you're asking what the original problem description was, it's at the bottom of this post. Columns A, B, and C contain thepivottable with fields Region (A) State (B) and Sales (C). Region and State are both Row area fields and Sales is the Values area field. Column D is the problem: How to get each State's percent of its Region sales. For example: Idaho is 90 percent of the North Region's sales. By the way, I came up with a nice solution to this problem, building on others' ideas. Here's the forumla entered in cell D5 and then just copied down in Column D to the last row of thepivottable in columns A:C. =C5/INDEX(A5:INDIRECT("C"&COUNTA(C:C)+3),MATCH("* total",A5:INDIRECT("A"&COUNTA(C:C)+3),0),3) For this to work you should have thePivottable arranged in Tabular form and the first row item should be in row 5 (column headings, page fields, etc. in rows 1 - 4). Also, don't override the defaultSubtotalnames since the formula looks for the literals total to find thesubtotalrows. And be sure to put the formula in col D before you do any filtering in thepivottable. If you make any big changes to thepivottable, just reenter the formula and copy it down again. "uslanja" wrote: Don, I'm trying to solve the same problem as Ted M H, I think, but not sure what the "base"pivottable looks like so I can apply your recommended cell formulas correctly. If either of you recall, would you post something more descriptive? Thanks, Jim "Don" wrote: This is a hard one for justpivottables. I would do the total by region in onepivottable then for thepivottable below create two columns 1) =IF(A3="",E2,A3) this will copy down the region from thepivottables 2) =C3/VLOOKUP(E3,$F$3:$G$4,2) This will look at the number in the sales column (c3) and divide it by looking at your table from F3:G4 and finding out the total for that region F g North 100 West 200 "Ted M H" wrote: I need to get mypivottable to return a percent ofsubtotalinstead of a percent of column. Here's what I want: Region State Sales Percent of Sales North Montana 10 10 % Idaho 90 90 % North Total 100 100 % West Calif 50 25 % Oregon 100 50 % Arizona 50 25 % West Total 200 100% I can easily do this using the Show Value As % of Column option with field settings for the Sales Field, but as the description implies this gives me just the percent of of the grand total for the column rather than for the subtotals. Is it possible to do what I want here? An alternative would be to use the GETPIVOTDATA function. The structure of the function is the essentially the same to look up a value or a values subtotal except that for the latter you just don't specify which specific value you're after. Set it up with a couple of lookups and you should be away. This is about the only use I've ever found for the perpetually annoying GETPIVOTDATA function! Rob |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivottable: Show value as a percent of Subtotal
Hey Ted - Thanks for posting your solution. I simply copied and pasted your
formula and it works fine except for the last group which returns and #N/A error. Using your example if you had North, South, East and West and West was the last group listed in the Pivot Table, all of formulas displaying the percentages for the Western States return the #N/A error. If I re-sort the table to West is not the last group, then formulas work correctly, and whateverother Region ends up as the last group gets the errors. I can't figure it out. Any thoughts? Thanks. Thanks, Andrew "Ted M H" wrote: Hi Jim, Not certain that I understand your question...If you're asking what the original problem description was, it's at the bottom of this post. Columns A, B, and C contain the pivot table with fields Region (A) State (B) and Sales (C). Region and State are both Row area fields and Sales is the Values area field. Column D is the problem: How to get each State's percent of its Region sales. For example: Idaho is 90 percent of the North Region's sales. By the way, I came up with a nice solution to this problem, building on others' ideas. Here's the forumla entered in cell D5 and then just copied down in Column D to the last row of the pivot table in columns A:C. =C5/INDEX(A5:INDIRECT("C"&COUNTA(C:C)+3),MATCH("* total",A5:INDIRECT("A"&COUNTA(C:C)+3),0),3) For this to work you should have the Pivot table arranged in Tabular form and the first row item should be in row 5 (column headings, page fields, etc. in rows 1 - 4). Also, don't override the default Subtotal names since the formula looks for the literals total to find the subtotal rows. And be sure to put the formula in col D before you do any filtering in the pivot table. If you make any big changes to the pivot table, just reenter the formula and copy it down again. "uslanja" wrote: Don, I'm trying to solve the same problem as Ted M H, I think, but not sure what the "base" pivot table looks like so I can apply your recommended cell formulas correctly. If either of you recall, would you post something more descriptive? Thanks, Jim "Don" wrote: This is a hard one for just pivot tables. I would do the total by region in one pivot table then for the pivot table below create two columns 1) =IF(A3="",E2,A3) this will copy down the region from the pivot tables 2) =C3/VLOOKUP(E3,$F$3:$G$4,2) This will look at the number in the sales column (c3) and divide it by looking at your table from F3:G4 and finding out the total for that region F g North 100 West 200 "Ted M H" wrote: I need to get my pivot table to return a percent of subtotal instead of a percent of column. Here's what I want: Region State Sales Percent of Sales North Montana 10 10 % Idaho 90 90 % North Total 100 100 % West Calif 50 25 % Oregon 100 50 % Arizona 50 25 % West Total 200 100% I can easily do this using the Show Value As % of Column option with field settings for the Sales Field, but as the description implies this gives me just the percent of of the grand total for the column rather than for the subtotals. Is it possible to do what I want here? |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivottable: Show value as a percent of Subtotal
Hi Andrew,
Just now read your post. I see the problem. The formula needs the pivot table data to start in row 5 for the INDIRECT function with its COUNTA argument to work. If you have a bunch of report filter/page filter items that cause the pivot table data to start at, say, row 7 instead of row 5, then change the formula so that where it now says +3 for the INDIRECT functions to say +5. See before and after formulas, below. Befo =C5/INDEX(A5:INDIRECT("C"&COUNTA(C:C)+3),MATCH("* total",A5:INDIRECT("A"&COUNTA(C:C)+3),0),3) After: =C5/INDEX(A5:INDIRECT("C"&COUNTA(C:C)+5),MATCH("* total",A5:INDIRECT("A"&COUNTA(C:C)+5),0),3) My first solution was simpler, but less elegant: =C5 / INDEX(A5:C$10000,MATCH("* total",A5:A$10000,0),3) This works great as long as your pivot table doesn't go beyond 10,000 rows. This seems a sloppy way to deal with the uncertainty of the number of rows in the pivot table, so I prefer the solution that uses the INDIRECT function to mark the end of the MATCH range. Let us know how you solve your problem€¦ "Andrew" wrote: Hey Ted - Thanks for posting your solution. I simply copied and pasted your formula and it works fine except for the last group which returns and #N/A error. Using your example if you had North, South, East and West and West was the last group listed in the Pivot Table, all of formulas displaying the percentages for the Western States return the #N/A error. If I re-sort the table to West is not the last group, then formulas work correctly, and whateverother Region ends up as the last group gets the errors. I can't figure it out. Any thoughts? Thanks. Thanks, Andrew "Ted M H" wrote: Hi Jim, Not certain that I understand your question...If you're asking what the original problem description was, it's at the bottom of this post. Columns A, B, and C contain the pivot table with fields Region (A) State (B) and Sales (C). Region and State are both Row area fields and Sales is the Values area field. Column D is the problem: How to get each State's percent of its Region sales. For example: Idaho is 90 percent of the North Region's sales. By the way, I came up with a nice solution to this problem, building on others' ideas. Here's the forumla entered in cell D5 and then just copied down in Column D to the last row of the pivot table in columns A:C. =C5/INDEX(A5:INDIRECT("C"&COUNTA(C:C)+3),MATCH("* total",A5:INDIRECT("A"&COUNTA(C:C)+3),0),3) For this to work you should have the Pivot table arranged in Tabular form and the first row item should be in row 5 (column headings, page fields, etc. in rows 1 - 4). Also, don't override the default Subtotal names since the formula looks for the literals total to find the subtotal rows. And be sure to put the formula in col D before you do any filtering in the pivot table. If you make any big changes to the pivot table, just reenter the formula and copy it down again. "uslanja" wrote: Don, I'm trying to solve the same problem as Ted M H, I think, but not sure what the "base" pivot table looks like so I can apply your recommended cell formulas correctly. If either of you recall, would you post something more descriptive? Thanks, Jim "Don" wrote: This is a hard one for just pivot tables. I would do the total by region in one pivot table then for the pivot table below create two columns 1) =IF(A3="",E2,A3) this will copy down the region from the pivot tables 2) =C3/VLOOKUP(E3,$F$3:$G$4,2) This will look at the number in the sales column (c3) and divide it by looking at your table from F3:G4 and finding out the total for that region F g North 100 West 200 "Ted M H" wrote: I need to get my pivot table to return a percent of subtotal instead of a percent of column. Here's what I want: Region State Sales Percent of Sales North Montana 10 10 % Idaho 90 90 % North Total 100 100 % West Calif 50 25 % Oregon 100 50 % Arizona 50 25 % West Total 200 100% I can easily do this using the Show Value As % of Column option with field settings for the Sales Field, but as the description implies this gives me just the percent of of the grand total for the column rather than for the subtotals. Is it possible to do what I want here? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
% of Subtotal in PivotTable | Excel Discussion (Misc queries) | |||
PivotTable - Percent of Current Total | Excel Discussion (Misc queries) | |||
Show Percent changes | Charts and Charting in Excel | |||
How do you show percent changes? | Charts and Charting in Excel | |||
How to calculate and then show up as a percent? | Excel Worksheet Functions |