Zip Codes in Multiple Service Areas

[Barcabarn7]

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!

[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

 

[edhans]

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.

[Barcabarn7]

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.

See my PBIX file.

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

[Barcabarn7]

Sorry,  this is not quite it unless there's another step I can't figure out. We'd need the Market A, B, and C markets to then only display the totals for the zip codes that are within their market.

I re-articulated my request in another reply but my previous statement is the end-goal "This" your asking for, I hope that makes sense.

I appreciate your assistance.

[Barcabarn7]

Thank you for your patience. I attempted to be specific in my initial inquiry but now see that could be difficult to interpret.

Below are screenshots (I made a sample powerbi file, can't see how to attach here.)

Ultimately,

1. we want a table that compares each Market's totals from the initial data screenshot. So, totals from zip codes in two markets would subtotal in both markets.

2. We want a single slicer that can select each market on a report page, that'd be able to select Mkt_A or Mkt_B and receive the subtotals from either market.

In the last screenshot, you'll see the markets aren't mutually exclusive to each, and currently provided in a matrix format. 

Does that make more sense? Thank you for your thoughtfullness!!!