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

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

mrhartley

Well-known Member
Joined
Mar 8, 2005
Messages
564
Hi and Welcome,

Try this puppy, it might be just what your looking for.

Regards
Mark
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,018
Messages
5,834,976
Members
430,331
Latest member
Syed Yasir Hannan

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