Cross-Sell Analysis

[gdecome]

Hi team, trying to do some cross-selling analysis and I have a  Sales table with columns ORDER, PRODUCT, AMOUNT  and need some suggestions to build the model and create DAX. No other table with Product details, just need to summarize by the Product name already included in the Sales table.

The sample (format) below is just to illustrate the content for better visualization:

Order1   Prod1,$02

Order2   Prod1,$05   Prod2,$10

Order3   Prod1,$02   Prod2,$04   Prod3,$01

Order4               Prod2,$05

Order5               Prod2,$10   Prod3,$02

Order6   Prod1,$08               Prod3,$04

Order7   Prod1,$04

Based on that, I want to show 3 matrixes with the combination of each pair of products.

By number of Orders, for example:

Prod1-Prod2 : 2 Orders include both products

Prod3-Prod3: No order for Prod3 alone

Table is symmetric by the diagonal

        Prod1   Prod2   Prod3

Prod1       3       2       2

Prod2       2       1       2

Prod3       2       2       0

By Amount of Products, for example:

Prod3-Prod1 : $15 is the amount for the 2 products when sold together

Prod2-Prod2: $5 is the total when Prod2 is sold alone

Table is symmetric by the diagonal

        Prod1   Prod2   Prod3

Prod1     $14     $21     $15

Prod2     $21     $05     $17

Prod3     $15     $17      $0

By Percentage of Amount, for example:

Prod1-Prod2 : 33% of the amount (from the 2 products) comes from Prod1

Prod3-Prod3: it doesn´t matter as it will be the same in the diagonal

Table is not symmetric but complementary by the diagonal

        Prod1   Prod2   Prod3

Prod1       -     33%     67%

Prod2     67%       -     82%

Prod3     33%     18%       - 

[v-diye-msft]

Hi @gdecome 

Sorry not quite understand the logic.

          Prod1   Prod2   Prod3

Prod1       3       2       2

Prod2       2       1       2

Prod3       2       2       0

eg: if you'd like to calculate the distinct count of orders when there's only row value&column value exist, the distinct count of order should like below:

If you'd like to calculate counts which contains row value/column value, the results should like below:

Could you please elaborate on how's going in your expected results?

[gdecome]

Hi @v-diye-msft , thank you for you feedback.

My logic is the following:

• In the diagonal, number should be the number of Orders where the there is only one product:
  Prod1 = 2 (Orders 1 and 7) ... I put 3 by mistake
  Prod2 = 1 (Order4 only)
  Prod3 = 0 (it is not the unique product in any Order)
• For the others combinations, some examples:
  Prod1-Prod2 = Prod2-Prod1 --> 2 ... Order2 and Order3 have both products
  Prod1-Prod3 = Prod3-Prod1 --> 2 ... Order3 and Order6 have both products
  No matter if the Order has more products, for this counting I just need to know if both are in the order
• For the calculations (Amount and %) the logic is the same considering pair of products or the product alone 

I hope this classify and, by your pictures, I see you already did some tests. Thank you very much for your help.

[gdecome]

Hi @v-diye-msft, after a review I better understand the two calculations you and I would say both make sense. What I´m thinking as result is a combination of two. The first for the diagonal (when product is the same in row/column) and the second table for the rest. Is it possible?

[v-diye-msft · Accepted solution]

Hi @gdecome 

It's not able to combine the 2 logics into one calculation, if you'd like to get the counts of which only have related prod items, using below measure:

Measure = var a = SELECTEDVALUE('Table'[Prod])
var b = SELECTEDVALUE('Table 2'[Prod])
var t =DISTINCT( UNION(ROW("Prod",a),ROW("Prod",b)) )
Return COUNTROWS(FILTER(all('Table'[Order]),CALCULATE(var t2=CALCULATETABLE(VALUES('Table'[Prod]),ALLSELECTED('Table'[Prod])) return COUNTROWS ( t2 ) = COUNTROWS ( t )
 && ISEMPTY ( EXCEPT ( t, t2 ) ))))

If you'd like to get the counts of which Inclusive of prod items, use this one:

Measure 2 = var t1 = SELECTCOLUMNS(FILTER(ALL('Table'),'Table'[Prod]=SELECTEDVALUE('Table 2'[Prod])),"order",[Order])

Return
CALCULATE(DISTINCTCOUNT('Table'[Order]),INTERSECT(t1,VALUES('Table'[Order]))

)

attached pbix for your ease.

[gdecome]

Hi @v-diye-msft , thank you very much for your support.

[harshnathani]

HI @gdecome ,

You can have a look at 

https://www.youtube.com/watch?v=8PUNpVAiRAE

https://www.youtube.com/watch?v=P7fHvMzM_ZM

https://www.youtube.com/watch?v=z9ttZAZkEhs

https://finance-bi.com/power-bi-basket-analysis/

https://www.daxpatterns.com/basket-analysis/

Regards,

Harsh Nathani

Appreciate with a Kudos!! (Click the Thumbs Up Button)

Did I answer your question? Mark my post as a solution!