% of Subtotal - Pivot Tables

[William]

See a pivot table like the example here:

http://www.mrexcel.com/tip015.shtml

How would you go about displaying the %'s as a % of the subtotal (see last pivot table).
Eg in West Total it says 79.01%. I want that to read 100% and in the line above appels to be displayed as 64/448 = 14.2% of the West total.

Any help much appreciated!

Regards,

William

 

[Mark W.]

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Region	Product	Sales			Data	Region
2	West	Cherries	256			Sales			%			TotalSales	Total%
3	West	Bananas	128		Product	Central	East	West	Central	East	West
4	West	Apples	64		Apples	64	1	64	57.14%	14.29%	14.29%	129	22.75%
5	Central	Apples	64		Bananas	16	2	128	14.29%	28.57%	28.57%	146	25.75%
6	Central	Cherries	32		Cherries	32	4	256	28.57%	57.14%	57.14%	292	51.50%
7	Central	Bananas	16		GrandTotal	112	7	448	100.00%	100.00%	100.00%	567	100.00%
8	East	Cherries	4
9	East	Bananas	2
10	East	Apples	1
11
Sheet1

 

[William]

Hi Mark,

thanks for your response. I can see that the data you display is the answer, but I have no idea how you accomplished the second part of the data table, titled %. Is this a derived field (which formula?) that you drag in the data part of the layout, or is this the Sales field shown as a % (which one?) and dragged to the column part of the layout?

Thanks again,

William

 

[Mark W.]

Sorry... I moved the 'Region' field button to the COLUMN area, dragged a 2nd copy of the 'Sales' field to the DATA area, double clicked on this 'Sales' field, renamed it to '%', clicked the [ Option>> ] button, and set the "Show data as" selection to "% of column".
This message was edited by Mark W. on 2002-11-18 17:36