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.
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.