cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Azizbek
Frequent Visitor

Revealing paired products. Calculating how many times of each product was sold with selected product

Hello,

I am working in retail food chain, and I faced with the issue of revealing paired products, like: sausage and bread, cookies and milk, and etc. In other words, revealing products that are mostly sold with another product. 

Below, the data structure sample that I have in Power BI:

Purchase_ID

Product_Name

1

Milk

1

Cookie

1

Bread

2

Cookie

3

Bread

3

Cookie

4

Milk

4

Cookie

5

Cookie

5

Bread

5

Sausage

6

Milk

6

Cookie

 

The result that I would like to reach in the dashboard view is below table:

If selected Product_Name Milk, the result would be below table:

Product name

Number of times
sold with selected product

Milk

3

Cookie

3

Bread

1

Sausage

0


Milk was sold with Milk 3 times in Purchuase_IDs: 1, 4 and 6;
Cookie was sold with Milk 2 times in Purchuase_IDs: 1 and 4;
Bread was sold with Milk 1 time in Purchuase_IDs: 1;
Sausage was not sold with Milk in the same Purchuase_ID, that's why is 0.

I was able to do it in EXCEL, but in Power BI I have not reached the result. The excel steps were:
1. Filter Product_Name column by MILK, and reveal values of Purchuase_ID that include MILK;
2. Remove all filters, add filter to Purchuase_ID column with values that I get in 1 step. 
3. Using Count function to reveal number of times of each product that has been sold with MILK

I look forward for your support.
Any help is appreciated. 

 

 

1 ACCEPTED SOLUTION
2 REPLIES 2

@amitchandra thank for your reply, It helped a lot, and I solved it. 

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!