Shows which products each customer has the potential to purchase

[Rai_BI]

Hello,

I'm having trouble building a report that makes customer product suggestions based on customer segmentation.

In my data model I have the following tables "fSales", "dCustomers", "dProducts", "dCalendar".

The customers in the "dCustomers" table have a column called "Segment" that informs which segment the customer belongs to.

I need to create a matrix table that shows which products each customer has the potential to purchase if they have not yet purchased.

For example, suppose that in the LABORATORY segment you have 100 customers and of these 100 customers, 80 FREQUENTLY buy the SYRINGE product. In this case, there are 20 customers who have the potential to buy the SYRINGA product, as they are customers who belong to the LABORATORY segment.

It is important to observe the frequency that the product is sold to avoid pollution, for example, if only one customer bought the product, this does not mean that other customers can also buy it. However, if several customers, for example 60% of customers in the segment, are buying the same product then it is likely that the others will also be able to buy it.

How can I do this in Power BI?

Thank you in advance

[MattAllington]

One of the reasons people find it hard to solve such problems is because they can't "see" a way forward. I argue that if this were Excel, one would not try to solve this problem in their head but instead would start writing formulas and adding them to the spreadsheet, solving a small piece of the problem with every formula. The trick to becoming good with Power BI (in my view) is to take the same approach. 

based on your description of the problem, I would:

1. set up a matrix with product on rows. 
2. To start with, I would add a slicer for segment, and select 1
3. write a measure for total customers (count rows of customer table) and add to the matrix. Check it works for different segments
4. write a measure for customers that purchased. There are a few ways. Maybe CALCULATE([total customers],CROSSFILTER(cust[id],sales[cust id],BOTH)).  Add it to the matrix.
5. Now you can "see" the components, it should be apparent how to calculate % of customers that purchased. Write that measure and add it to the matrix.
6. Write a measure "customers that haven't purchased" and add it
7. write the measure you need using the % of customers that purchased in an if statement. Something like IF([% of cust]>0.3,[customers that haven't purchased])

then you need to check the totals work for products and segments. My guess is segments will work but products won't. You will likely need to wrap the measures where totals don't add up inside a SUMX, something like SUMX(VALUES(products],[cust that purchased])

read about this here https://exceleratorbi.com.au/use-sum-vs-sumx/

the process is as important as the final working formula, so give it a go. 

[MattAllington]

What defines "frequently"?  You need a clear rule to be able to solve this. 

[Rai_BI]

@MattAllington For now I'm just thinking about the number of customers who purchased the product (for example 60% of customers in the segment). Once I understand how I can build the deashboard, then in the future I can add more criteria to make the deashboard more intelligent.