Forum Discussion

PaulDBrown's avatar
PaulDBrown
Icon for Community Champion rankCommunity Champion
8 years ago

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

Hi everyone,

 

I'm struggling with DAX in trying to achieve a more elegant/performance-effective solution. 

 

The objective (outcome I'm looking for):

"what is the projected sales based on the performance of estimates over sales?" Which in simple terms means I'm trying to calculate a number for sales for Items for which there are no "estimates" based on how  items which do have  "estimates" vary against their  sales (if that makes sense...).

 The problem I'm encountering is that in order to get the final result, including totals, I'm having to use two measures with SUMX, which doesn't seem very performance-effective.

 

So is there a more elegant solution?

 

Here is a dummy table:

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

And here is the sample PBIX file:

 

 

Projected Sales

 

Thank you in advance for your kind help!

 

Regards,

Paul.

Need Help

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    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