cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Regular Visitor

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 6
Highlighted
Super User VI
Super User VI

Re: Zip Codes in Multiple Service Areas

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.

edhans_0-1596727445631.png

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.

edhans_1-1596727506399.png

 

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
Highlighted
Solution Sage
Solution Sage

Re: Zip Codes in Multiple Service Areas

@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

 

ZipCode.PNG 

 

 

Highlighted
Regular Visitor

Re: Zip Codes in Multiple Service Areas

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 CodeSales
4411125
4411235
4411345
4414455
4411135
4411250
4411360
4414480
44111100
44112200
44113300
44144400

 

And the zip code market map that our company uses. (I ignore the primary secondary, eg 44111 = Market A, and Market B). 

2.

Zip CodeMarket AMarket BMarket C
44111PrimarySecondary 
44112 Primary 
44113Primary  
44144 PrimarySecondary

 

 

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!

Highlighted
Super User VI
Super User VI

Re: Zip Codes in Multiple Service Areas

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.

edhans_0-1596730767550.png

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.

edhans_1-1596730866531.png

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

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
Highlighted
Regular Visitor

Re: Zip Codes in Multiple Service Areas

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. 

 

 

Barcabarn7_0-1596809031419.png

Barcabarn7_1-1596809062553.png

Barcabarn7_2-1596809115766.png

 

 

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

 

Highlighted
Regular Visitor

Re: Zip Codes in Multiple Service Areas

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.

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Find your favorite faces from the community presenting at the Power Platform Community Conference!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors