Calculate Percent of Subtotal In Pivot Table

[dcoons]

Hello,

It does not appear that subtotal percentages are possible in a pivot table using a calculated field. Is this true? (If so, what a glaring oversight by Microsoft). I've tried a variety of options, none of which produce the desired result, which is a subtotal percentage that adds to 100 percent and will recalculate as the table is changed. This seems to be a fairly common problem (without a solution!) judging from the posts I've seen

I have been able to successfully use Andrew Poulsom's great formula solution found elsewhere in this forum. It is a good solution for tables where all possible values for a subtotal will always be used, but it does not adjust/recalculate if the pivot table changes (in the example below, if one were to exclude TV, the subtotals in Chicago, for example, would not recalculate so that the market total for the remaining Internet and Radio would remain 100%)

Is there a way to do this with a calculated field, or a way that will adjust if a member of the subtotal is excluded?

Note in the image below, the pivot table "Amt" column comes from the Amout column in the spreadsheet, and the pivot table "Pct Mkt" is the sum of the "MktSubTotForm" column (uses Andrew's formula) in the spreadsheet side. This is the column I would like to replace with a calculated field or some other 'adjustable method' if there is one.

Thanks for looking,
Dale.

Book1
	A	B	C	D	E	F	G	H	I
1	Market	Medium	Amount	MktSubTotForm				Data
2	Chicago	Internet	24	3.31		Market	Medium	Amt	MktPct
3	Chicago	Internet	240	33.06		Chicago	Internet	264	36.4
4	Chicago	Radio	22	3.03			Radio	242	33.3
5	Chicago	Radio	220	30.30			TV	220	30.3
6	Chicago	TV	20	2.75		ChicagoTotal		726	100.0
7	Chicago	TV	200	27.55		LosAngeles	Internet	385	46.7
8	LosAngeles	Internet	35	4.24			Radio	275	33.3
9	LosAngeles	Internet	350	42.42			TV	165	20.0
10	LosAngeles	Radio	25	3.03		LosAngelesTotal		825	100.0
11	LosAngeles	Radio	250	30.30		NewYork	Internet	330	50.0
12	LosAngeles	TV	15	1.82			Radio	220	33.3
13	LosAngeles	TV	150	18.18			TV	110	16.7
14	NewYork	Internet	30	4.55		NewYorkTotal		660	100.0
15	NewYork	Internet	300	45.45		GrandTotal		2211	300.0
16	NewYork	Radio	20	3.03
17	NewYork	Radio	200	30.30
18	NewYork	TV	10	1.52
19	NewYork	TV	100	15.15
Sheet1

 

[Andrew Poulsom]

You could try this formula in D2 copied down:

=IF(ISNUMBER(MATCH(B2,G:G,FALSE)),C2/SUMPRODUCT(--(A$2:A$19=A2),--(ISNUMBER(MATCH(B$2:B$19,G:G,FALSE))),C$2:C$19)*100,0)

Note that you will need to refresh the pivot table after you change what's shown in the Medium field, because the formula refers to that column. You could use the Worksheet_Calculate event procedure to automate it:

Private Sub Worksheet_Calculate()
    Application.EnableEvents = False
    Me.PivotTables(1).PivotCache.Refresh
    Application.EnableEvents = True
End Sub