Percent of Pivot Table Question

[rconverse]

If I have a row label of product groups and vendors and want to know what the percentage of sales within each group is, how would I accomplish that?

If I do % of column for sales, it does it for the whole column. I want each subtotal to equal 100%.

Something like below but in pivot form.

Paper Products
Vendor A - 100k in sales - 50%
Vendor B - 50k in sales - 25%
Vendor C - 50K in sales - 25%
Total Paper Products - 200k in sales - 100%
Mops
Vendor D - 200k in sales - 67%
Vendor E - 50k in sales - 16%
Vendor F - 50K in sales - 17%
Total Paper Products - 300k in sales - 100%....

Thanks,
Roger

 

[sr12345]

Which version of Excel are you using?

Do you have the ability to add a column to the data set?

 

[rconverse]

2007

I could, but I kind of need it to be dynamic so if I change the row sources, I can still produce a percentage of subtotals.

 

[sachavez]

This will work for Excel 2007

Pull your sales numbers into the values field
set the value settings as a "% of Total"

Let us know if that works.

 

[sachavez]

This will work for Excel 2007

Pull your sales numbers into the values field
set the value settings as a "% of Total"

Let us know if that works.

Excel Workbook
	A	B	C	D
3			Values
4	SHIPPER	PRICE AUTHORITY	Sum of UNITS	% of Total
5	TOYOTAMOTSAL	35062	24	0.07%
6		90020	2	0.01%
7		111123	24	0.07%
8		111965	1	0.00%
9		112128	586	1.75%
10		112300	684	2.05%
11		147201	27	0.08%
12		300996	25103	75.09%
13		307113	1	0.00%
14		307191	6568	19.65%
15	TOYOTAMOTSAL Total		33020	98.77%
16	TOYOTATSUAME	94002	407	1.22%
17		111332	2	0.01%
18		305116	3	0.01%
19	TOYOTATSUAME Total		412	1.23%
20	Grand Total		33432	100.00%
Sheet5

 

[rconverse]

Thanks but I'm looking for both of your Toyota group's percent of sales that tallies to 100%.

 

[bigalkesn]

I am looking to do the same thing, the percent of subtotals. Have you found an answer yet?

Scot

 

[sachavez]

Scot,

I could not find a solution and moved on to conquer a bigger rock.

Steve