Calculate sale of other products in the same basket of a specific product

[drbumba]

Hello everyone,

I have a transaction table with multiple products and sale numbers. I try to know the connection between one product and the remaining products, if they are likely to buy togethers. Im not good at english so I will give you a sample table and my expected result:

Transaction_ID	Product_ID	Sale
HD001	P1	200
HD001	P2	300
HD001	P3	200
HD002	P1	400
HD002	P2	100
HD003	P3	600

I want to get a pivot table which show me the sum of other product in the same basket as one specific products:
- P1:

   + P2: 400 (300 in HD001, 100 in HD 002)

   + P3: 200 (200 in HD001)

- P2:

   + P1: 600

   + P3: 200

- P3:

   + P1: 200

   + P2: 300

I hope you all be safe, thanks.

[Anonymous]

// Assumptions:
// For a single product we want to see 
// the sum of all other products in the
// baskets in which the product exists.
// Bear in mind that you have not shown
// any dimensions, so I have to write
// a measure that will only work correctly
// with the one table presented. If
// your model is different from this one
// table, you'll have to modify the DAX
// accordingly.

[Other Prod Amount] =
IF( HASONEFILTER( T[Product_ID] ),
    // Since you've selected product P
    // the sum will be the total amount
    // for P across all the baskets.
    var __sumOfProductInBaskets =
        sum( T[Sale] )
    // This sum is the total for all the
    // baskets that are currently visible.
    var __sumOfAllBasketsWithTheProduct =
        CALCULATE(
            SUM( T[Sale] ),
            VALUES( T[Transaction_ID] ),
            ALL( T )
        )
    // This difference gives you the total
    // across the baskets in which P exists
    // excuding the value of P.
    var __delta =
        __sumOfAllBasketsWithTheProduct
            - __sumOfProductInBaskets
    var __result =
        If( __delta, __delta )
    return
        __result
)

[drbumba]

Thank you for your reply. Unfortunately the end result im seeking is the amount for each pair it's generating. So the other dimension is other products. The end table should look like this.

	P1	P2	P3	Total
P1	0	400	200	600
P2	600	0	200	800
P3	200	300	0	500

Your code did great job showing the total each product generate. But i cant see how much P1 make P2 sale for example.

I wrote a reply yesterday but it wasnt uploaded. Im sorry if making you inconvenient.

[Anonymous]

// For the analysis you want you have to
// have at least 3 tables. The one with
// transactions (baskets) and one dimension
// with all the products, call it Products,
// and one other dimension called 'Other Products'.
// The latter will be a copy of the former.
// You'll connect Products to your Transactions table
// on ProductID and will leave the other
// table DISCONNECTED.
// Here is the relationship you need:
// Products[ProductID] 1 <-one-way- * Transactions[ProductID]
// You should hide all columns in Transactions.
// If you want, you could leave only the TransactionID
// column visible so that you can slice by transactions
// (baskets).
//
// Once the above is in place, you can write a measure:

[Other Product Sales] =
IF( HASONEVALUE( Products[ProductID] ),        
    var __otherProds =
        VALUES( 'Other Products'[ProductID] )
    return
        // This returns the value of the other products
        // in all the baskets in which the main product
        // can be found in the current context.
        CALCULATE(
            SUM( Transactions[Sale] ),
            // This filters the Transactions for
            // the other products
            TREATAS(
                __otherProds,
                Products[ProductID]
            ),
            // Restores the values of TransactionID's
            // visible in the current context (these
            // are the baskets in which the main product
            // can be found)
            VALUES( Transactions[TransactionID] ),
            // Removes all filters from Transactions
            ALL( Transactions )
        )
)

[amitchandak]

@drbumba , are looking for Market Basket Analysis
https://finance-bi.com/power-bi-basket-analysis/
https://businessintelligist.com/2017/03/27/market-basket-analysis-using-power-bi/
https://www.daxpatterns.com/usecases/market-basket-analysis/
https://www.mssqltips.com/sqlservertip/5428/market-basket-analysis-in-r-and-power-bi/

[drbumba]

Thanks for your advices, the basket analysis is meaningful. But i still need the number result as I mentions, because we have other application requiring that calculation. Thank you.

[Anonymous]

Wait a bit. It's not really that hard to write the correct measure but I have to find the time. Also, the table you've posted is not a representative one. Can you please send a better one with many more rows and many more different cases? For the time being, the table only shows baskets where all products are present. This, of course, is not what happens in real life. Also, for such a new table would you please give a description of what you want to see in each different case? That would certainly help to write the correct formula... Thanks.

[Greg_Deckler]

@drbumba - Can you just use a matrix visualization and put transaction and product_id in the rows hierarchy?

[drbumba]

I tried the matrix visualiaztion but the outcome is different from what i expected. The value should be the sum of other product sale base on a specific items. The matrix filter out all other value of others products leaving only the chosen one. Do you have any other ideas.

[drbumba]

I never thought of that. I will try and report back later, thanks 😄