This use case describes a sub case of a 'Market Basket Analysis' that deals with how to identify pair of products that has high probability rate that will be purchased together.
This information is very beneficial for companies that can based their marketing strategy on it: suggest bundle of the two products, locate them closely on a physical store, etc.
Let's say that you have the following list of transactions which describes sales of product, date, quantity, price and the total $ amount:
Each record represents transaction and product (1 out of 9).
In order to understand what is the probability to sell product y with product x (P(Y=y|X=x)) I created a new custom table with the following query:
SELECT t1.*, t2.[Product Id] AS [Related Product]
FROM [Transaction] t1
LEFT JOIN [Transaction] t2 ON t1.[Ttransaction Id]=t2.[Ttransaction Id] AND t1.[Product Id]<>t2.[Product Id]
This allowed me to create the following widget:
while using the following formula for the 'Mutual Basket Probability':
[# of Transaction Id]/([# of Transaction Id],all([Related Product]))
Please find Attached Excel, ecdata and dash files.