Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Just another comment.... Doesnt have to return a single most accompanying product it can be a table based output then just arrange by highest count of accompanying occurances.
So somthing like
Item Purchasing Accompanying Product Count of Occurences
0001 0010 150
0001 0027 111
... and so on.
Just not sure how to preform the calculations, ground the item number by order reffernce and count of line items.
Thanks,
J
Hi @Anonymous,
If your data are stored in the same table, you can simply summary them by summarize function.
For example:
Summary table= Summarize(Table,Table[Produce ID], "Count of Cutomer", DISTINCTCOUNT(Table[Customer ID]), "Count of Occurentces",Count(Table[Order ID]))
In addition, if above not help, can you provide the table structure and some sample data to test?
Regards,
Xiaoxin Sheng
Hi Xiaoxin,
Attached a small sample of Data above,
The OrderID is the Order Number so i effectively want to Group the Order Numbers and then find the partern of when items are bought together.
So then if a customer calls and wants Product 1, we enter the product 1 Item Number into a lookup and then it searches all the grouped Order numbers and finds how many times Product 1 is mentioned on the same Order ID as Product X.
Then in the visual / table it returns the product number and description of the product that is mentioned on the same orderID as Product 1 the most.
The aim is to have a place we can search an item number and it return the item number of a good accompanying product or show that two items are frequently bought together.
So if Customer A is buying a single product we can say, hey most people buy item number 3 with item 1 alot would you be interested.
Thanks in advance,
J
Your dummy data only had 4 transactions with multiple products (125 OrderIDs and 121 distinct OrderIDs). I adapted the solution found here: https://community.powerbi.com/t5/Desktop/Generating-a-matrix-of-interactions/m-p/115378/highlight/tr... to generate a table that gives the count of every product combination. You can then sort on the resulting count to see which products were sold most frequently together, but please remember that this does not adjust for the frequency of the first item.
In other words, if Item A is purchased 100 times, and Item B is purchased with Item A 10 times, then Item B is purchased with A 10% of the time. However, if Item C is purchased 10 times, and Item D is purchased with Item C 5 times, it will be purchased with Item C 50% of the time, yet the AB combination will "rank" higher because 10 > 5.
To overcome this scenario, you want to look into Apriori algorithm for association rules mining. Conviniently there is a custom R visual that does this automatically; you just need to get each transaction on a row with the basket of items purchased for each transaction in a second field. You would then need to split that basket into the first product and remaining, but let's cross that bridge when/if we get there.
Sorry i realised afterwards it was a bad sample of data, i just took the most recent 125 entries from an export.
How did you adapt the solution found on the other page? Although effectively we do want the same thing but for different reasons so should work with a few tweaks.
The code in the accepted solution there, how would i use that?
Is that the code for a Pivot Table in excel?
Thanks for the help, hours ive been thinking how to achieve this! Crazy what talent is on here, helped me no end learning DAX and PowerBI!
Cheers,
J
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.
Hi DK
Coming to it now we are there, with what you mentioned here;
"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."
Is there a way to show the sample size count in the table too?
So Product X has been sold 200 times, with Product A 40 times, Product B 60 times and Product C 60 times?
Generally it wont matter to much as this report will be designed to be breaking down one product at a time, but having a sample size will come in handy for a % visual / calculation rather than showing just the number?
If you happen to know,
Thanks,
J
Hi Dkay,
That worked perfectly, the image of the tabel made it clear was a query edited table, had me confused for a moment.
Thanks for all the help!
Cheers,
J
Just to be clear, you are not interested in association rule mining but just getting the most frequently purchased accompanying product for every product in your data set?
OrderID | ItemNumber | Item Description | Item Category |
174015 | 1.51 | Product 1 | PARTS |
169758 | 2.01 | Product 2 | PARTS |
166535 | 2.012 | Product 2.5 | PARTS |
121489 | 8.6 | Product 3 | PARTS |
126539 | 8.6 | Product 3 | PARTS |
121489 | 8.603 | Product 3.5 | PARTS |
75706 | 61 | Product 4 | PARTS |
126971 | 95.05 | Product 5 | PARTS |
111257 | 100 | Product 6 | PARTS |
116023 | 100 | Product 6 | PARTS |
117978 | 100 | Product 6 | PARTS |
117959 | 100 | Product 6 | PARTS |
144462 | 100 | Product 6 | PARTS |
148966 | 100 | Product 6 | PARTS |
150958 | 100 | Product 6 | PARTS |
159990 | 100 | Product 6 | PARTS |
170414 | 100 | Product 6 | PARTS |
160545 | 100 | Product 6 | PARTS |
167188 | 100 | Product 6 | PARTS |
172864 | 128 | Product 7 | PARTS |
186313 | 128 | Product 7 | PARTS |
90450 | 128 | Product 7 | PARTS |
96172 | 128 | Product 7 | PARTS |
130874 | 130 | Product 8 | PARTS |
97033 | 133 | Product 9 | PARTS |
97213 | 133 | Product 9 | PARTS |
97214 | 133 | Product 9 | PARTS |
97757 | 133 | Product 9 | PARTS |
99121 | 133 | Product 9 | PARTS |
99201 | 133 | Product 9 | PARTS |
93079 | 133 | Product 9 | PARTS |
93080 | 133 | Product 9 | PARTS |
93588 | 133 | Product 9 | PARTS |
93681 | 133 | Product 9 | PARTS |
93448 | 133 | Product 9 | PARTS |
94271 | 133 | Product 9 | PARTS |
94793 | 133 | Product 9 | PARTS |
95753 | 133 | Product 9 | PARTS |
95226 | 133 | Product 9 | PARTS |
149380 | 137.106 | Product 10 | PARTS |
149381 | 137.106 | Product 10 | PARTS |
161876 | 137.106 | Product 10 | PARTS |
149380 | 137.128 | Product 10.5 | PARTS |
149381 | 137.128 | Product 10.5 | PARTS |
161876 | 137.128 | Product 10.5 | PARTS |
60935 | 178 | Product 11 | PARTS |
110164 | 186.01 | Product 12 | PARTS |
49037 | 186.01 | Product 12 | PARTS |
73244 | 186.01 | Product 12 | PARTS |
107976 | 186.01 | Product 12 | PARTS |
109433 | 186.01 | Product 12 | PARTS |
53747 | 219 | Product 13 | PARTS |
63531 | 219 | Product 13 | PARTS |
103658 | 219.01 | Product 13.5 | PARTS |
115901 | 219.01 | Product 13.5 | PARTS |
135187 | 219.01 | Product 13.5 | PARTS |
190881 | 219.01 | Product 13.5 | PARTS |
58140 | 219.01 | Product 13.5 | PARTS |
80581 | 219.01 | Product 13.5 | PARTS |
193113 | 219.01 | Product 13.5 | PARTS |
187278 | 219.4 | Product 13.6 | PARTS |
213389 | 219.4 | Product 13.6 | PARTS |
155728 | 219.6 | Product 13.7 | PARTS |
177560 | 219.6 | Product 13.7 | PARTS |
185282 | 219.6 | Product 13.7 | PARTS |
176270 | 219.75 | Product 13.8 | PARTS |
95958 | 223 | Product 14 | PARTS |
109507 | 242.34 | Product 15 | PARTS |
130133 | 242.34 | Product 15 | PARTS |
154162 | 242.34 | Product 15 | PARTS |
180282 | 242.34 | Product 15 | PARTS |
214095 | 242.34 | Product 15 | PARTS |
44355 | 242.34 | Product 15 | PARTS |
46116 | 242.34 | Product 15 | PARTS |
45609 | 242.34 | Product 15 | PARTS |
47847 | 242.34 | Product 15 | PARTS |
68701 | 242.34 | Product 15 | PARTS |
79470 | 242.34 | Product 15 | PARTS |
91095 | 242.34 | Product 15 | PARTS |
162476 | 242.34 | Product 15 | PARTS |
70127 | 242.34 | Product 15 | PARTS |
71762 | 242.34 | Product 15 | PARTS |
74371 | 242.34 | Product 15 | PARTS |
76014 | 242.34 | Product 15 | PARTS |
93674 | 242.34 | Product 15 | PARTS |
97210 | 242.34 | Product 15 | PARTS |
76365 | 242.44 | Product 15.5 | PARTS |
209591 | 242.46 | Product 15.6 | PARTS |
61896 | 248.04 | Product 16 | PARTS |
110238 | 248.32 | Product 16.1 | PARTS |
137394 | 248.32 | Product 16.1 | PARTS |
105613 | 248.34 | Product 16.2 | PARTS |
110061 | 248.34 | Product 16.2 | PARTS |
111118 | 248.34 | Product 16.2 | PARTS |
99400 | 248.34 | Product 16.2 | PARTS |
137374 | 248.34 | Product 16.2 | PARTS |
138346 | 248.34 | Product 16.2 | PARTS |
145167 | 248.34 | Product 16.2 | PARTS |
154497 | 248.34 | Product 16.2 | PARTS |
157361 | 248.34 | Product 16.2 | PARTS |
172712 | 248.34 | Product 16.2 | PARTS |
190559 | 248.34 | Product 16.2 | PARTS |
192295 | 248.34 | Product 16.2 | PARTS |
197627 | 248.34 | Product 16.2 | PARTS |
203732 | 248.34 | Product 16.2 | PARTS |
204077 | 248.34 | Product 16.2 | PARTS |
204309 | 248.34 | Product 16.2 | PARTS |
205155 | 248.34 | Product 16.2 | PARTS |
207322 | 248.34 | Product 16.2 | PARTS |
208059 | 248.34 | Product 16.2 | PARTS |
209605 | 248.34 | Product 16.2 | PARTS |
229481 | 248.34 | Product 16.2 | PARTS |
43322 | 248.34 | Product 16.2 | PARTS |
43523 | 248.34 | Product 16.2 | PARTS |
51987 | 248.34 | Product 16.2 | PARTS |
52155 | 248.34 | Product 16.2 | PARTS |
54519 | 248.34 | Product 16.2 | PARTS |
55329 | 248.34 | Product 16.2 | PARTS |
54934 | 248.34 | Product 16.2 | PARTS |
56632 | 248.34 | Product 16.2 | PARTS |
59564 | 248.34 | Product 16.2 | PARTS |
59640 | 248.34 | Product 16.2 | PARTS |
61177 | 248.34 | Product 16.2 | PARTS |
62204 | 248.34 | Product 16.2 | PARTS |
70969 | 248.34 | Product 16.2 | PARTS |
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
60 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |