Forum Discussion
Overlap table
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
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.).
6 Replies
Anonymous ,
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.
Anonymous ,
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.
