Pivot Table Top 10 Limitation

TheFoxman

New Member
Joined
Dec 2, 2005
Messages
2
I am trying to use a pivot table to summarize source data containing markets (cities), stores within those markets, and store sales.

I need the pivot table to display the top 10 stores by market. I know how to do this, this is the easy part. Upon choosing the autoshow and top 10 I can get my top 10 stores and a subtotal for them. However, I also need a grandtotal for ALL stores in a market (inclusive of the top 10). I would use this grandtotal of all stores to do market share percentages. Marketshare is based on the percentage of the entire market, not the summation of the top 10. There are countless stores per market, thus the need to only list out the top 10.

I have searched the boards, internet, help menus extensively and can only derive that "subtotals for pivot tables are based on what is selected (in this case, the top 10) only."

Some clever workarounds have included altering the source data through the use of the RANK function and sorting on that as a field with a bucket of "other" if it is not in the top 10. In my case however, I don't need "other" (not top 10) I need ALL (including top 10). Further, my source data is huge and I am unsure how to efficiently use the RANK function to rank stores within each market.

Any help or out of the box ideas would be greatly appreciated.
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,558
Latest member
aivin

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
Back
Top