Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Optimizing the SUMX()

Hi, I have the requirement for finding the product of 2 columns record wise which can be achieved by using SUMX(). I have a table of around 10 million records, and performing a Sumx() for the entir...
Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi Anonymous ,

     

    Here are some optimizations you can try:

    1. The SUM function is used instead of the SUMX function, because the SUMX function iteratively calculates each row, while the SUM function only needs to sum the entire column, so the SUM function is faster to calculate.
    2. Make sure your data model is optimized, including using the appropriate data types, removing unnecessary columns, creating indexes, and more.
    3. Try using filters or queries in Power Query Editor to reduce the amount of data that needs to be calculated.
    4. Store data in Azure Analysis Services, which allows compute to be moved to the cloud and reduces the burden on on-premises computing.

                                                                                                                                                             

    Best Regards,

    Stephen Tao

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.           

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Hi Anonymous ,

 

Here are some optimizations you can try:

  1. The SUM function is used instead of the SUMX function, because the SUMX function iteratively calculates each row, while the SUM function only needs to sum the entire column, so the SUM function is faster to calculate.
  2. Make sure your data model is optimized, including using the appropriate data types, removing unnecessary columns, creating indexes, and more.
  3. Try using filters or queries in Power Query Editor to reduce the amount of data that needs to be calculated.
  4. Store data in Azure Analysis Services, which allows compute to be moved to the cloud and reduces the burden on on-premises computing.

                                                                                                                                                         

Best Regards,

Stephen Tao

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.