Percent of Pivot Table Question

rconverse

Well-known Member
Joined
Nov 29, 2007
Messages
1,187
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
 

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

sr12345

Active Member
Joined
Feb 20, 2009
Messages
317
Which version of Excel are you using?

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

rconverse

Well-known Member
Joined
Nov 29, 2007
Messages
1,187
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

Active Member
Joined
May 22, 2009
Messages
446
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

Active Member
Joined
May 22, 2009
Messages
446

ADVERTISEMENT

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
ABCD
3Values
4SHIPPERPRICE AUTHORITYSum of UNITS% of Total
5TOYOTAMOTSAL35062240.07%
69002020.01%
7111123240.07%
811196510.00%
91121285861.75%
101123006842.05%
11147201270.08%
123009962510375.09%
1330711310.00%
14307191656819.65%
15TOYOTAMOTSAL Total3302098.77%
16TOYOTATSUAME940024071.22%
1711133220.01%
1830511630.01%
19TOYOTATSUAME Total4121.23%
20Grand Total33432100.00%
Sheet5
 

rconverse

Well-known Member
Joined
Nov 29, 2007
Messages
1,187
Thanks but I'm looking for both of your Toyota group's percent of sales that tallies to 100%.
 

bigalkesn

New Member
Joined
Nov 10, 2009
Messages
2
I am looking to do the same thing, the percent of subtotals. Have you found an answer yet?

Scot
 

sachavez

Active Member
Joined
May 22, 2009
Messages
446
Scot,

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

Steve
 

Watch MrExcel Video

Forum statistics

Threads
1,122,494
Messages
5,596,486
Members
414,070
Latest member
DuncanLucas

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top