Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
I'm relatively new at using Power BI, so I hope someone can help me with this question.
I want to make a overlap table of certain products on zip code level.
Example:
I want to make this dataset:
Zip code Product
1000 A
1000 B
1001 A
1001 B
1002 C
1003 B
1003 C
1004 A
1005 B
1006 C
1007 C
1008 A
1009 B
1010 A
Into this table:
A | B | C | |
A | 5 | 2 | 0 |
B | 2 | 5 | 1 |
C | 0 | 1 | 4 |
Thanks in advance
Solved! Go to Solution.
Than you for your help! However this is just a subset. Doing this for the whole dataset would take quite a lot of time, because there are many combinations possible.
In another topic I found this:
This did not bring me to the exact table I wanted, so I did the rest by hand. However this took quite a lot of hand work, because all all possible orderings of products appeared in different cells (a,b,c - a,c,b - b,a,c - etc.).
Could you please explain the number in the matrix?
A | B | C | |
A | 5 | 2 | 0 |
B | 2 | 5 | 1 |
C | 0 | 1 | 4 |
Regards,
Jimmy Tao
The 5 upper left means that there are in total 5 zipcodes with product A.
The 2 upper middle means that there are in total 2 zipcodes with as well product A as product B.
The upper right means that there are no zipcodes with as well product A as product C.
I'm mainly interested in the overlap (how many people (zipcodes) have more than 1 product and which product combinations occur most often), so the values for A-A, B-B and C-C are not that important to me. It's mainly about the rest of the values in the matrix.
So your requirement is to achieve the distinct count of zipcode with three combinations "A&B", "B&C" and "A&C", right? You can create three calculate columns and three measures using DAX below:
Calculate columns:
Combination A&B = CALCULATE(CONCATENATEX('Table', 'Table'[Product], "&"), FILTER(ALLEXCEPT('Table', 'Table'[Zip Code]), 'Table'[Product] = "A" || 'Table'[Product] = "B")) Combination B&C = CALCULATE(CONCATENATEX('Table', 'Table'[Product], "&"), FILTER(ALLEXCEPT('Table', 'Table'[Zip Code]), 'Table'[Product] = "B" || 'Table'[Product] = "C")) Combination A&C = CALCULATE(CONCATENATEX('Table', 'Table'[Product], "&"), FILTER(ALLEXCEPT('Table', 'Table'[Zip Code]), 'Table'[Product] = "A" || 'Table'[Product] = "C"))
Measures:
Count A&B = CALCULATE(DISTINCTCOUNT('Table'[Zip Code]), FILTER('Table', 'Table'[Combination A&B] = "A&B")) Count B&C = CALCULATE(DISTINCTCOUNT('Table'[Zip Code]), FILTER('Table', 'Table'[Combination B&C] = "B&C")) Count A&C = CALCULATE(DISTINCTCOUNT('Table'[Zip Code]), FILTER('Table', 'Table'[Combination A&C] = "A&C"))
The result will be like below:
Community Support Team _ Jimmy Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Than you for your help! However this is just a subset. Doing this for the whole dataset would take quite a lot of time, because there are many combinations possible.
In another topic I found this:
This did not bring me to the exact table I wanted, so I did the rest by hand. However this took quite a lot of hand work, because all all possible orderings of products appeared in different cells (a,b,c - a,c,b - b,a,c - etc.).
Thanks for your reply!
There isn't another option in for example the query editor or by using a measure or something to get this done? Is it better to use another program, for example excel to do this? I also tried to do it in excel, but I don't know how to do it in excel either.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |