How to implement market basket analysis

Question

We have customer details table with data like&nbsp;OrderID&nbsp;&nbsp;&nbsp;&nbsp; ProductName&nbsp;&nbsp;&nbsp;&nbsp; Sales1001&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; a, b &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; $201002 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; a,c &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; $201003 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; a,b &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; $201004 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; a,c &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; $201005 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; a,b &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; $20&nbsp;I need a Market Basket as when I select product A, it should display a list as:Product&nbsp;&nbsp;&nbsp;&nbsp; OrdersB&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3C&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2&nbsp;This means, Product B is purchased 3 times with product A and similarly product C is purchased 2 times with product A.I have a dashborad created in tableau. I need to create a replicate of it in powerbi.Please follow this link: https://ibb.co/dquarv&nbsp;

v-huizhn-msft · Answer

Hi&nbsp;Akshay,There is no direct&nbsp;market basket analysis visual in Power BI desktop. But you can achieve your requirement by following the steps.1. Right click the table-&gt;Query Editor, split the product name by selecting "split columns"-&gt;By Delimiter-&gt;comma shown in the screenshot. Then click apply on homw page.Here is my query statement.
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQwMFRQ0lFK1EkCkkYGSrE6YEEjsFgyipgxFnUmWNSZoqiLBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [OrderID = _t, #" ProductName" = _t, Sales = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"OrderID", Int64.Type}, {" ProductName", type text}, {"Sales", Int64.Type}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", " ProductName", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {" ProductName.1", " ProductName.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{" ProductName.1", type text}, {" ProductName.2", type text}})
in
    #"Changed Type1"
2. Create a slicer including ProductName.1, create a measure using the formula below. Then create a table visual to display the expected result.
Orders = COUNTA(Table1[ ProductName.2])
I upload my .pbix file as attachment, please download and review more details.Best Regards,Angelia

maabdullah47 · Answer

Thank you for the Post but There is no PBX file attached with this post , can you upload it?

Forum Discussion

How to implement market basket analysis

2 Replies

Related content

Cohort analysis

Marketing Campaign Analysis Template

MDM implementation

Bank Marketing Analysis

Marketing Campaign Analysis