Overlap table

[dickvankl]

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

[v-yuta-msft]

@dickvankl ,

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

[dickvankl]

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.

[v-yuta-msft]

@dickvankl ,

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.

[dickvankl · Accepted 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:

https://community.powerbi.com/t5/Desktop/Sum-customers-by-product-combination-buyed-a-b-a-c-b-c-or-a...

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.).

[Anonymous]

Hi @dickvankl , As I have in mind, you can not put the same field in columns and rows in a matrix.

[dickvankl]

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.