Calculate 'Surrounding Market' for all stores in same Zip Codes as the Chain selected using the Slicer

[PowerPivotDave]

I’m using PowerPivot with Excel 2010. My dashboard contains a slicer to select the Main Chain name, which of course will show performance metrics for that chain (products growing, products declining, and so on…)

I would also like to show the combined performance for all other stores that fall within the same Zip Codes as the Main Chain’s stores, but I have been struggling with this problem for a couple days.

For example, the Main Chain selected using the slicer could have 500 stores across the country, and in the same zip codes as those 500 stores there are an additional 2,000 stores made up of other chains. I would like those additional 2,000 stores to automatically form a group (the ‘Surrounding Market’) to compare vs. the Main Chain. If a user selected a different Main Chain, the ‘Surrounding Market’ should update based on the zip codes of the Main Chain.

All data is in 1 table right now:
Store # – Chain Name – Zip Code – Product Category – Product – Units Sold


Any help would be greatly appreciated...Thanks.

 

[scottsen]

It is going to sorta depend on exactly what you want to do w/ the info.

Easy case:
=CALCULATE([Total Units], ALLEXCEPT(MyTable, MyTable[ZipCode]))

Right, that would strip away all info about chains and stores n such... and only respect the zipcodes. Giving you the total units sold across all stores in the zip.

However, that is not going to actually "show" which stores/chains we are talking about.

That's trickier.

My gut is you will need a disconnected slicer on Chain Name (else slicing Safeway is removing Albertsons). I also think you will have to do some clever modeling (not just 1 table), and then vaguely general idea will be to get a measure that can tell you "Is this store in the same zipcode as the selected chain?" And you will use that measure to sorta "post-filter" the pivot table. Like, drop down the pivot table menu, select value filters, and it will let you filter on the results of that measure to hide rows you don't care about.

That's my gut and totally not-fleshed-out and probably non-that-helpful initial thinking. If I get a chance later today, I'll try and play with it...

 

[PowerPivotDave]

Thanks for taking a stab at this.

Here is my latest attempt. Learning some new, fun things by the way!....hopefully the explanation below is easy to follow. I'm still fighting through this so your help is really appreciated.

Using 3 Measures
Total Units:=sum([Net Units])
Total Units All Chains:=CALCULATE([Total Units], ALL(MyTable[Chain Name]
Total Units minus Main Chain:=[Total Units All Chains] - [Total Units]

Total Units shows: "the main chain selected"
Total Units minus Main Chain shows: "all other chains/stores in those same zip code"

Added Dimensions Tables
I created Dimension tables for Chain Name, Product Category, Product with a relationship back to MyTable (fact table). My Pivot Tables seem to only work when the Dimension tables are used as my Pivot Table Row Labels.

2 Slicers
On the dashboard I have slicers for both Chain & Zip Code. It's a 2 step slicer process, select Chain, and then select the Zip Codes (which is basically then just clicking on all the zip codes that are not lightly shaded). I have both of these slicers linked to all the Pivot Tables. It's not really clean because some large Chains have hundreds of zip codes to select, but this 2 slicer trick is basically a work-around to find "all other stores in the same zip code", but ultimately this dashboard is used by a few hundred people, and somehow I will need to remove this step in the final product.

I could be way off - only time will tell...

 

[scottsen]

I'm wondering if you are going to need clever techniques like this: Filters CAN Flow Up Hill – Via Formulas That Is « PowerPivotPro

You have a workbook you are willing to share via dropbox/google drive/whatever-msft-thing-is-called ?