topic Re: Calculate sale of other products in the same basket of a specific product in DAX Commands and Tips

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

drbumba — Wed, 26 Aug 2020 02:39:52 GMT

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.

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

Greg_Deckler — Wed, 26 Aug 2020 03:00:12 GMT

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

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

amitchandak — Wed, 26 Aug 2020 05:13:44 GMT

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/

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

drbumba — Wed, 26 Aug 2020 05:14:28 GMT

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

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

drbumba — Thu, 27 Aug 2020 02:42:16 GMT

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.

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

drbumba — Thu, 27 Aug 2020 02:45:40 GMT

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.

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

Anonymous — Thu, 27 Aug 2020 08:02:19 GMT

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.

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

Anonymous — Thu, 27 Aug 2020 12:06:02 GMT

// 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 )

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

drbumba — Fri, 28 Aug 2020 04:01:10 GMT

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.

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

Anonymous — Fri, 28 Aug 2020 09:31:06 GMT

// 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 ) ) )