topic Re: Sales for the customers who bought Product A & B in DAX Commands and Tips

Sales for the customers who bought Product A & B

rob_vander2 — Tue, 18 Mar 2025 13:45:13 GMT

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

Re: Sales for the customers who bought Product A & B

Deku — Tue, 18 Mar 2025 13:54:20 GMT

Sumx(

Values(table[custID]),

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

Return

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

)

Re: Sales for the customers who bought Product A & B

rob_vander2 — Tue, 18 Mar 2025 15:04:22 GMT

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

Re: Sales for the customers who bought Product A & B

andrewsommer — Tue, 18 Mar 2025 16:00:55 GMT

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.

Re: Sales for the customers who bought Product A & B

rob_vander2 — Tue, 18 Mar 2025 16:08:42 GMT

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

Re: Sales for the customers who bought Product A & B

Deku — Tue, 18 Mar 2025 16:15:34 GMT

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

Re: Sales for the customers who bought Product A & B

sjoerdvn — Tue, 18 Mar 2025 17:36:38 GMT

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 )))

Re: Sales for the customers who bought Product A & B

rob_vander2 — Wed, 19 Mar 2025 16:43:17 GMT

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