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
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

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

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.