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