DAX Challenge

Question

Hi&nbsp;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.&nbsp;Sales Table:Order#Customer#Item#Sales$1ABCITM A1001ABCITM B202BCDITM A1002BCDITM C152BCDITM D203DEFITM X2003DEFITM Y204FGHITM A1004FGHITM C15&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Upselling Part Detail&nbsp;Master ProdutUpsellable ProductITM AITM BITM AITM CITM XITM Y&nbsp;&nbsp;&nbsp;Final Report Will Look SOmething Like thisReport Filter:&nbsp; Master Product : ITM ATotal Orders: 3&nbsp;Upsold Product# Of Orders% Upsold ( Total Order of ITM &lt;&gt; / Total Order)ITM B11/3 = 33%ITM C22/3 = 66%&nbsp;&nbsp;&nbsp;&nbsp;Any Help is much appreciated.Thanks&nbsp;

greg_deckler · Accepted Answer

Anonymous&nbsp;Probably something like:
&nbsp;
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)
  
&nbsp;
&nbsp;
&nbsp;

Forum Discussion

DAX Challenge

2 Replies

Related content

getting challenges with data science

No CACULATE challenge

DAX Challenge

DAX challenge (nested if or switch is not working)

DAX Aggregation Total Challenge

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

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

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