Commonly co-ordered products

[Anonymous]

I am trying to determine a way to quantify the product co-ordering between different invoices.

Essentially, for a given product, what other products have been ordered with it, and how many times were the products ordered together

Example Data

Invoice number     Product

11111                     Product A

11111                     Product B

11111                     Product C

11111                     Product D

22222                    Product C

22222                    Product E

33333                    Product A

33333                    Product E

33333                    Product F

Selection/Filter: Product A

Product A     2 (this should equal the total count of that product ordered)

Product B     1

Product C     2

Product D     1

Product E      0

Product F      0

With a specific product selected, I want look at the other Invoices where that product was ordered, and determine the other products co-ordered, then calculate the frequency.

So with this example; for invoices where Product A was ordered,

Products B, C and D have been co-ordered, and C is the most commonly co-ordered product.

Another example

Selection/Filter: Product C

Part A     2

Part B     1

Part C     3  (this should equal the total count of that product ordered)

Part D     1

Part E      1

Part F      1

[sunny_talwar]

I am not getting the exact same results as you have put down, but can you explain why Product E and Product F are 0? In order 33333 we have A, E, F.

Expression:

=Count(DISTINCT {<[Invoice number] = p([Invoice number]), Product>}[Invoice number])

[Anonymous]

@ Sunny T

My mistake, I made an error when I copied the example over.

Do your answers look like this?

Selection/Filter: Product A (yep it look like this one does as you have shown above)

Product A     2 (this should equal the total count of that product ordered)

Product B     1

Product C     1

Product D     1

Product E     1

Product F     1

Selection/Filter: Product C

Part A     1

Part B     1

Part C     2  (this should equal the total count of that product ordered)

Part D     1

Part E     1

Part F      0

It works, however I think I need to do some deeper analysis on my data set to confirm

[sunny_talwar]

Sure, see if it work or not