Forum Discussion
Building a overlap Matrix in PBI
Hi,
I'm trying to build an overlap matrix that shows the count of overlapping items (their DISTINCT COUNT) being sold at different stores, but can't seem to figure it out and couldn't find anything when searching for it in this forum. Here is an example of what the table should look like plus adding some highlighting (gradient cells) to almost having some sort of a heatmap.
| Store Item Overlap Count | Store1 | Store2 | Store3 | Store4 |
| Store 1 | 1000 | 200 | 100 | 500 |
| Store 2 | 200 | 500 | 50 | 360 |
| Store 3 | 100 | 50 | 200 | 100 |
| Store 4 | 500 | 360 |
100 | 750 |
This is the dataset I'm working with, which doesn't have any counts in them so it's possible to differentiate:
| Stores | Item ID |
| Store 1 | x123456789 |
Store 2 | x123456789 |
| Store 3 | a98765432 |
| Store 4 | a98765432 |
| Store 2 | a98765432 |
| Store 3 | c328291733 |
| Store 1 | o329172789 |
| Store 4 | o329172789 |
| ... | ... |
I'd be happy to get some help on this or just some tips on how to tackle this as I feel stuck with this issue.
Thanks!
2 Replies
Hi! How about something like this where you show the products in rows and in columns the stores so you can quickly see how many stores carry the same item?
Hi, that would be an option if the number of items wouldn't be significant. In my case we have a couple thousand items so the view would be not ideal and having a general overview on the overlapping item count is best.
Thank you!
