Forum Discussion
Zip Codes in Multiple Service Areas
Hi everyone,
I am working with market share data in which a single zip code exists in multiple service areas that aren't mutually exclussive. I'd like to have a table that shows the following
Market A - Total Sales
Market B - Total Sales
Market C - Total Sales
44102 exists in both market A and B, I'm intending to map the totals to both market A and B.
44102 - Market A
44102 - Market B
44103 - Market A
44104 - Market C
Etc...
Ultimately, this relationships will be used to create a filter (which I can do already), but also display visual variances that includes totals that'd show duplicative totals for 44102 shown in both market A and B and I'm stumped!
Any help would be greatly appreciated!
6 Replies
- edhans
Community Champion
Can you create a unique MarketZip column in each? In Power Query just add a new column with the formula [Market] & [Zip Code] and make it text. Do this in both tables.
You then have fields in both your Market table (top) and Zip code table (bottom) that you can join. It is a 1:1 so bi-directional filtering is automatic here.
Thank you!
I might not be proficient enough with powerquery to figure out how to concatenate the zip code from my data table with the market shares.
For example, here's a sample of my data table:
1.
Zip Code Sales 44111 25 44112 35 44113 45 44144 55 44111 35 44112 50 44113 60 44144 80 44111 100 44112 200 44113 300 44144 400 And the zip code market map that our company uses. (I ignore the primary secondary, eg 44111 = Market A, and Market B).
2.
Zip Code Market A Market B Market C 44111 Primary Secondary 44112 Primary 44113 Primary 44144 Primary Secondary I can make the unique key and unpivot the Zip-Map, but I can't figure out how to get the unique "zip - market" key in the 1st data table?
Thank you so much!
- edhans
Now that I've seen your data I would take a different approach. I can get this matrix, which to me seems not super informative, but it does correspond to the data you gave, as every zip has every market.
I created a model that looks like this. In Power Query I created a Zip Dim table that will control both the Zip sales and Market Fact tables.
If you need further help, can you let us know what your end goal is? I feel like I am doing a bit of guessing as to where you are going. In other words, "I have this data, how can I get this visual?" vs "I have this data, and need to do this to it" when "this" may not get you to your goal. Make sense?
How to get good help fast. Help us help you.
How to Get Your Question Answered Quickly
How to provide sample data in the Power BI Forum
- negi007
Barcabarn7 There is a workaround but not sure if it is what you want to achieve. Create a calculated column by combinaing zip code and market and then use this column in the legend and sales volume in bubble size as below
