Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi
Not sure how to desribe this... but i want to find common accompanying products.
So i have years and years of Sales Data with multiple Order ID's but unique Product Numbers, what i want to do is have a product number lookup and it return the most common accompanying product.
So for example, Customer A calls and wants to order Product Item 0001, we then punch the Product Item into the lookup and it returns saying 2000 customers bought the same Product number 0001 with product number 0010.
So then we can advise customer A, product 0010 is a great accompanying product and boost sales based on what we know is working from all our other customers.
Hope that makes sense, and any help would be great...
Not sure if there is a ready made set of visuals or if the data insights tool can be manipulated in any way...
Thanks in Advance,
J
Solved! Go to Solution.
This will generate a regular table, not a pivot table, but it will be dynamic with regards to new transactions being added to your transactions table. The following is the code for your dummy data:
let func = (Table) => let Source = Table, #"Removed Other Columns" = Table.SelectColumns(Source,{"Item Description"}), AllProducts = Table.Distinct(#"Removed Other Columns", {"Item Description"}), Performancewise = Table.AddColumn(AllProducts, "Performancewise", each 1), #"Merged Queries" = Table.NestedJoin(Performancewise,{"Performancewise"},Performancewise,{"Performancewise"},"NewColumn",JoinKind.LeftOuter), #"Expanded NewColumn" = Table.ExpandTableColumn(#"Merged Queries", "NewColumn", {"Item Description"}, {"Item Description.1"}), AllCombinations = Table.RemoveColumns(#"Expanded NewColumn",{"Performancewise"}), #"Added Custom1" = Table.AddColumn(AllCombinations, "CombinationList", each Text.Combine(List.Sort({[Item Description],[Item Description.1]}))), #"Removed Duplicates" = Table.Distinct(#"Added Custom1", {"CombinationList"}), #"Added Custom" = Table.AddColumn(#"Removed Duplicates", "Filter", each if[Item Description.1]=[Item Description] then "out" else "in"), #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Filter] = "in")) in #"Filtered Rows", Source=Table1, #"Grouped Rows" = Table.Group(Source, {"OrderID"}, {{"OrderProducts", each _, type table}}), #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each func([OrderProducts])), #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Item Description", "Item Description.1"}, {"Item Description", "Item Description.1"}), #"Grouped Rows1" = Table.Group(#"Expanded Custom", {"Item Description", "Item Description.1"}, {{"Count", each Table.RowCount(_), type number}}), #"Filtered Rows" = Table.SelectRows(#"Grouped Rows1", each ([Item Description] <> null)) in #"Filtered Rows"
Just change the "Table1" in red above to the name of your transactions table.
Also, I filtered the resulting table to remove the null values (these are the 121 transactions with no paired item).
This is the result:
Please be weary of this result, as I mentioned earlier, due to no normalization of data for differing frequencies of purchase. Product 10.5 appears to sell more frequently with Product 10 than 3.5 with 3, but if Product 10 has sold 1000 times and Product 3 only 5 times, then think about what has a higher cross selling probability.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
117 | |
104 | |
77 | |
73 | |
52 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |