Forum Discussion
Overlap table
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.).
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.
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.).