Sales for the customers who bought Product A & B

[rob_vander2]

Hi All,

I want to calculate sum of sales for those customers who bought both the Product A & B. Please see the sample data expected output in the screenshot

[sjoerdvn · Accepted solution]

You could also try this one below. Don't know if it is better then the one using the intersect, but prefer it over using SUMX.

CALCULATE(SUM(Sales[Sales])
	,FILTER(VALUES(Sales[CustID]),AND(
		CALCULATE(COUNTROWS(Sales),Sales[Product]="A")>0
		,CALCULATE(COUNTROWS(Sales),Sales[Product]="B")>0
)))

[rob_vander2]

This seems faster than other. Sumx is slower than all.

[andrewsommer]

Product A&B Sales = CALCULATE(SUM(Sales[Sales]),FILTER,ALL(Sales[Product]), Sales[Product] in {“A”,”B”))

Please mark this post as solution if it helps you. Appreciate Kudos.

[rob_vander2]

@andrewsommer  This will not work. It's goving all customers who purchase A or B. I want customers who purchased A & B. 

[rob_vander2]

@Deku  I have got the below DAX as well
which one will be better?

VAR __custwithProductA = CALCULATETABLE(VALUES(Sales[CustID]), Sales[Product] ="A" )
VAR __custwithProductB = CALCULATETABLE(VALUES(Sales[CustID]), Sales[Product] ="B" )
VAR __CustwithProductA_B = INTERSECT(__custwithProductA,__custwithProductB)
VAR __Results = CALCULATE(SUM(Sales[Sales]), Sales[CustID] in __CustwithProductA_B)

RETURN __Results

[Deku]

Hard to say. Would need to run in Dax studio and look at server timings. Doubt much difference 

[Deku]

Sumx(

Values(table[custID]),

Var products = calculatetable( values( tables[product] )

Return 

If( "A" in product && "B" in products, table[sales])

)