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.
 

Some videos you may like

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

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
 

Watch MrExcel Video

Forum statistics

Threads
1,118,532
Messages
5,572,729
Members
412,482
Latest member
arooshrana2
Top