find the companion products / cross check

[datiduke]

Hi,
I have the task to identify the best companion product for each product. Its an Add-On Sale analysis. I've got the main product "gumboots" and would like to know, what are the most sold products with the gumboots. (e.g. socks, rainjacket, ... ).

What I have is a regular sales transaction with product, invoice#, date, etc.

I can think of an outcome something like:

500 gumboot sales: and thereof are
100 sales with red socks
70 sales with blue socks
20 sales with rainjacket model hurricane

Got two million rows of data, but don't know how I can make the cross link between the main product (gumboot) to the total other products sold on the same invoices.

Anyone an idea?

Cheers,
Andy

 

[sheetspread]

Something like this?

Excel 2010
	A	B
1	Product	Sales
2	A	7
3	B	4
4	C	8
5	A	1
6	A	1
7	G	1
8	H	0
9	A	2
10	H	4
11	B	3
12	G	0
13	A	9
14	B	9
15	C	5
16	A	9
17	H	5
18	G	1
19	B	2
20	G	7
21	B	9
22	B	1
23	B	7
24
25	Most Sold	B
Sheet25 (2)

Cell Formulas
Range	Formula
B25	{=INDEX($A$2:$A$23,MATCH(TRUE,SUMIF($A$2:$A$23,$A$2:$A$23,$B$2:$B$24)=MAX(SUMIF($A$2:$A$23,$A$2:$A$23,$B$2:$B$24)),0))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[sheetspread]

(the 24 should be a 23 even though it still works)

Oh sorry, didn't see that this was a BI question

 

[sheetspread]

The principle is the same in SQL for instance (a subquery with sum, top, or max), I'll see if I can find a reference to DAX

 

[datiduke]

yes, I use PowerPivot and would need a working DAX formula. I don't mind having two or more tables and link between them if that gets the task done.

Andy

 

[sheetspread]

How about one table with sums of each product, then another that takes the max sum of that table?

 

[datiduke]

how'd you get the Excel table in???

The result I am after is the total sales of each product:
Product: Transactions:
Main Product 5000
Companion P1 900
Companion P2 500
Companion P3 200
...

I want to know all of them, not just the most sold product.

Don't know how you wanna do it with sum and max... I'll try anything.

 

[sheetspread]

Download the first folder here: https://onedrive.live.com/?cid=8cffdec0ce27e813&id=8CFFDEC0CE27E813!189

DAX has a CALCULATE function that will give you all the totals, subtotaling in pivot tables with or without powerpivot is easy.

 

[Ozeroth]

"Basket Analysis" on DAX Patterns might be relevant here:Basket Analysis – DAX Patterns