A more effective solution (DAX)?: how to avoid using 2x SUMX

Hi PaulDBrown,

I'm not recommend you to use complex measure for in sumx or other summary function which contains if statement.(calculate count will multiplied = current row count * measure calculation count)

If measure nested multiple times, the calculation will increase to very huge amount. It will obviously cut down calculate performance.

I'd like to suggest you take a look at below blogs which told about how to increase performance when you nested use measures.

Reference link:

Optimizing DAX expressions involving multiple measures

Order of Evaluation in CALCULATE Parameters

BTW, you can also considered to sue summarize function to create variable tables to stored previous result, then calculate on summarised table. It will cut down calculation amount.

Regards,

Xiaoxin Sheng