DAX Challenge

[Anonymous]

Hi

I'm working on a project where I'll have to find the Number of times a Product has been upsold. For Example, If you buy a car, the sales man will pursue you to buy accesories like All Weather Mats, Roof Rail.. etc. I need to find out how many times All Weather mats are upsold with the particular Car, Like Wise for other accesories as well.

Sales Table:

Order#	Customer#	Item#	Sales$
1	ABC	ITM A	100
1	ABC	ITM B	20
2	BCD	ITM A	100
2	BCD	ITM C	15
2	BCD	ITM D	20
3	DEF	ITM X	200
3	DEF	ITM Y	20
4	FGH	ITM A	100
4	FGH	ITM C	15

Upselling Part Detail

Master Produt	Upsellable Product
ITM A	ITM B
ITM A	ITM C
ITM X	ITM Y

Final Report Will Look SOmething Like this

Report Filter:  Master Product : ITM A

Total Orders: 3

Upsold Product	# Of Orders	% Upsold ( Total Order of ITM <> / Total Order)
ITM B	1	1/3 = 33%
ITM C	2	2/3 = 66%

Any Help is much appreciated.

Thanks

[Greg_Deckler · Accepted solution]

@Anonymous Probably something like:

Measure = 
  VAR __Upsold = MAX('Table2'[Upsellable Product])
  VAR __MasterProducts = DISTINCT(SELECTCOLUMNS(FILTER('Table2',[Upsellable Product]=__Upsold),"Master",[Master Product]))
  VAR __Table1 = SELECTCOLUMNS(FILTER('Table1',[Item#] = __Upsold),"Order#",[Order#])
  VAR __Table2 = SELECTCOLUMNS(FILTER('Table1',[Item#] IN __MasterProducts),"Order#",[Order#])
RETURN
  COUNTROWS(INTERSECT(__Table1,__Table2)) / COUNTROWS(__Table1)
  

[amitchandak · Accepted solution]

@Anonymous , refer

Market Basket Analysis
https://finance-bi.com/power-bi-basket-analysis/
https://businessintelligist.com/2017/03/27/market-basket-analysis-using-power-bi/
https://www.daxpatterns.com/usecases/market-basket-analysis/
https://www.mssqltips.com/sqlservertip/5428/market-basket-analysis-in-r-and-power-bi/

[Greg_Deckler · Accepted solution]

@Anonymous Probably something like:

Measure = 
  VAR __Upsold = MAX('Table2'[Upsellable Product])
  VAR __MasterProducts = DISTINCT(SELECTCOLUMNS(FILTER('Table2',[Upsellable Product]=__Upsold),"Master",[Master Product]))
  VAR __Table1 = SELECTCOLUMNS(FILTER('Table1',[Item#] = __Upsold),"Order#",[Order#])
  VAR __Table2 = SELECTCOLUMNS(FILTER('Table1',[Item#] IN __MasterProducts),"Order#",[Order#])
RETURN
  COUNTROWS(INTERSECT(__Table1,__Table2)) / COUNTROWS(__Table1)