Get top N items that are bought together with a specific item

[tdanielspbi]

Hello everyone,

I am trying to calculate the top N items that are frequently bought together and the total sales associated with those items using Power BI. I have already tried many different DAX formulas, but I can't seem to figure it out. Unfortunately, as I'm live connected to an SSAS, I can't create new tables. 

My data structure is as follows: I have one fact table with all my sales transactions. This table has a many-to-one relationship with the items, customers, and date table. Here's an example of my fact sales data table:

Order Id Item Id Invoiced Quantity Sales Amount

1	A	2	20
1	B	1	10
2	A	1	10
2	C	3	30
2	D	2	20
3	A	4	40
3	C	1	10
3	E	2	20

What I want to achieve is a visualization that shows the top N items that are frequently bought together, along with the total sales associated with those items. So in the example above, Product A is bought with B, C and E. 

I have already tried several DAX formulas, including the one given in https://www.daxpatterns.com/basket-analysis/. However, my data model won't allow me to use this example. I have also tried using the SUMMARIZE function to group the data by order ID and create a new table, but I'm not sure how to proceed from there.

I

would greatly appreciate any guidance on how to approach this problem in Power BI.

Thank you in advance!

[tdanielspbi]

Thanks a lot @tamerj1 . I'm gonna work on this and let you know the result:) 

[tamerj1]

Hi @tdanielspbi 

Can you please clarfy how exactly would you visual look like? Do you want to have the topn combinations? or for each product, you want to have the topn other products that are sold along with this project?

[tdanielspbi]

Hi @tamerj1, , 

Thanks for the swift reply! I already have a visual on a page that shows top 10 items sold this year. However, for upselling possibilies I want to showcase items that are frequently bought together with those top selling items. Ideal would be that when you select one item from the visual showcasing top selling items, a table would be filled with other items and the associated sales. 

[tamerj1]

Hi @tdanielspbi 
Please refer to attached sample file with the proposed solution.

You need to have a disconnected product table that will be used in the new table visual. Please ley me know if you need any help on this.

Sales Amount = 
VAR SelectedItems = VALUES ( 'Table'[Item ID] )
VAR RelatedItems = 
    CALCULATETABLE ( 
        VALUES ( 'Table'[Item ID] ),
        ALL ( 'Table'[Item ID] ),
        VALUES ( 'Table'[Order ID] )
    )
VAR RelatedItemsOnly = EXCEPT ( RelatedItems, SelectedItems )
VAR FilterTable = 
    FILTER ( 
        VALUES ( 'Bought With Products'[Item ID] ),
        'Bought With Products'[Item ID] IN RelatedItemsOnly
    )
RETURN
    CALCULATE ( 
        SUM ( 'Table'[Sales Amount] ),
        TREATAS ( 
            FilterTable,
            'Table'[Item ID]
        )
    )

[tdanielspbi]

Hi@tamerj1,

I appreciate your effort in providing a solution, but I was hoping for something more specific. Instead of displaying all sales, I was expecting to see the total sales linked with the respective Order IDs. To illustrate this, let's consider the example of item B. If I choose Item B, the expected outcome would be the total sales of 20 for product A, instead of the overall sales of 70.

Thank you.

[tdanielspbi · Accepted solution]

I managed it to work add another statement to filter the RelatedOrders only. 

Thanks a lot @tamerj1  for your work!

This is the code

(Basket) Revenue for Items that are bought with Top Item = 

    VAR SelectedItems = VALUES ( Sales Table[Item ID] )
    VAR RelatedItems = 
        CALCULATETABLE ( 
            VALUES ( Sales Table[Item ID] ),
            ALL ( Sales Table[Item ID] ),
            VALUES ( Sales Table[Order ID] )
        )
    VAR RelatedOrders = 
        CALCULATETABLE ( 
            VALUES ( Sales Table[Order ID] ),
            ALL ( Sales Table[Order ID] ),
            VALUES ( Sales Table[Item ID] )
        )
    VAR RelatedItemsOnly = EXCEPT ( RelatedItems, SelectedItems )
    VAR FilterTable = 
        FILTER ( 
            VALUES ( 'Bought With Items'[Item ID] ),
            'Bought With Items'[Item ID] IN RelatedItemsOnly
        )
    RETURN
        CALCULATE ( 
            SUM ( Sales Table[Sales Amount] ),
            FILTER( ALL ( Sales Table ), Sales Table[Order ID] in RelatedOrders),
            TREATAS ( 
                FilterTable,
                Sales Table[Item ID]
            )
        )