Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Finding Data insights by product refference

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

1 ACCEPTED 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:

Capture.PNG

 

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.

View solution in original post

10 REPLIES 10
Anonymous
Not applicable

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Anonymous
Not applicable

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.

Anonymous
Not applicable

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:

Capture.PNG

 

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.

Anonymous
Not applicable

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

Anonymous
Not applicable

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?

Anonymous
Not applicable

OrderIDItemNumberItem DescriptionItem Category
1740151.51Product 1PARTS
1697582.01Product 2PARTS
1665352.012Product 2.5PARTS
1214898.6Product 3PARTS
1265398.6Product 3PARTS
1214898.603Product 3.5PARTS
7570661Product 4PARTS
12697195.05Product 5PARTS
111257100Product 6PARTS
116023100Product 6PARTS
117978100Product 6PARTS
117959100Product 6PARTS
144462100Product 6PARTS
148966100Product 6PARTS
150958100Product 6PARTS
159990100Product 6PARTS
170414100Product 6PARTS
160545100Product 6PARTS
167188100Product 6PARTS
172864128Product 7PARTS
186313128Product 7PARTS
90450128Product 7PARTS
96172128Product 7PARTS
130874130Product 8PARTS
97033133Product 9PARTS
97213133Product 9PARTS
97214133Product 9PARTS
97757133Product 9PARTS
99121133Product 9PARTS
99201133Product 9PARTS
93079133Product 9PARTS
93080133Product 9PARTS
93588133Product 9PARTS
93681133Product 9PARTS
93448133Product 9PARTS
94271133Product 9PARTS
94793133Product 9PARTS
95753133Product 9PARTS
95226133Product 9PARTS
149380137.106Product 10PARTS
149381137.106Product 10PARTS
161876137.106Product 10PARTS
149380137.128Product 10.5PARTS
149381137.128Product 10.5PARTS
161876137.128Product 10.5PARTS
60935178Product 11PARTS
110164186.01Product 12PARTS
49037186.01Product 12PARTS
73244186.01Product 12PARTS
107976186.01Product 12PARTS
109433186.01Product 12PARTS
53747219Product 13PARTS
63531219Product 13PARTS
103658219.01Product 13.5PARTS
115901219.01Product 13.5PARTS
135187219.01Product 13.5PARTS
190881219.01Product 13.5PARTS
58140219.01Product 13.5PARTS
80581219.01Product 13.5PARTS
193113219.01Product 13.5PARTS
187278219.4Product 13.6PARTS
213389219.4Product 13.6PARTS
155728219.6Product 13.7PARTS
177560219.6Product 13.7PARTS
185282219.6Product 13.7PARTS
176270219.75Product 13.8PARTS
95958223Product 14PARTS
109507242.34Product 15PARTS
130133242.34Product 15PARTS
154162242.34Product 15PARTS
180282242.34Product 15PARTS
214095242.34Product 15PARTS
44355242.34Product 15PARTS
46116242.34Product 15PARTS
45609242.34Product 15PARTS
47847242.34Product 15PARTS
68701242.34Product 15PARTS
79470242.34Product 15PARTS
91095242.34Product 15PARTS
162476242.34Product 15PARTS
70127242.34Product 15PARTS
71762242.34Product 15PARTS
74371242.34Product 15PARTS
76014242.34Product 15PARTS
93674242.34Product 15PARTS
97210242.34Product 15PARTS
76365242.44Product 15.5PARTS
209591242.46Product 15.6PARTS
61896248.04Product 16PARTS
110238248.32Product 16.1PARTS
137394248.32Product 16.1PARTS
105613248.34Product 16.2PARTS
110061248.34Product 16.2PARTS
111118248.34Product 16.2PARTS
99400248.34Product 16.2PARTS
137374248.34Product 16.2PARTS
138346248.34Product 16.2PARTS
145167248.34Product 16.2PARTS
154497248.34Product 16.2PARTS
157361248.34Product 16.2PARTS
172712248.34Product 16.2PARTS
190559248.34Product 16.2PARTS
192295248.34Product 16.2PARTS
197627248.34Product 16.2PARTS
203732248.34Product 16.2PARTS
204077248.34Product 16.2PARTS
204309248.34Product 16.2PARTS
205155248.34Product 16.2PARTS
207322248.34Product 16.2PARTS
208059248.34Product 16.2PARTS
209605248.34Product 16.2PARTS
229481248.34Product 16.2PARTS
43322248.34Product 16.2PARTS
43523248.34Product 16.2PARTS
51987248.34Product 16.2PARTS
52155248.34Product 16.2PARTS
54519248.34Product 16.2PARTS
55329248.34Product 16.2PARTS
54934248.34Product 16.2PARTS
56632248.34Product 16.2PARTS
59564248.34Product 16.2PARTS
59640248.34Product 16.2PARTS
61177248.34Product 16.2PARTS
62204248.34Product 16.2PARTS
70969248.34Product 16.2PARTS

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.