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 entire table resulted in "Visual has exceeded available resources".
To avoid this issue, I used Summarize() to only pick relevant columns instead of the entire table. The chart is now loading but it takes a lot of time for the visual to load.

Can anyone pls suggest how can we optimize this further? The chart takes around 1 minute to load.

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.           

5 Replies

  • DataVitalizer's avatar
    DataVitalizer
    Super User
    3 years ago

    Hi Anonymous 
    Since your are creating a measure I guess the visual would take time to appear completely, try using a calculated column, this would help you win in term of loading time over size.

     

    Did it work ? 👌 Mark it as a solution to help spreading knowledge 👉 A kudos would be appreciated

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Hi DataVitalizer 

      Since one of the value used for sumx is dynamically calculated , doing  SUMX using calculated column will give wrong result.

      • DataVitalizer's avatar
        DataVitalizer
        Super User
        3 years ago

        In a calculatd column you would not need to aggragate values (SUMX), just a basic calculation would do the job then you can use the column in your visual instead of the measure.

         

        Did it work ?  Mark it as a solution to help spreading knowledge  A kudos would be appreciated

  • 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.