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
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!
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 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
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:
And the zip code market map that our company uses. (I ignore the primary secondary, eg 44111 = Market A, and Market B).
|Zip Code||Market A||Market B||Market C|
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!
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?
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.)
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!!!
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.
Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.
Check out a full recap of the month!
Join this community-driven Power Platform digital event for unbiased support and problem-solving.
Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.
Visit our Community Blog for articles, guides, and information created by fellow community members.