Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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!
@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
Proud to be a Super User!
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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingThank 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!
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
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingSorry, 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.
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!!!