Forum Discussion
Optimizing the SUMX()
Hi Anonymous ,
Here are some optimizations you can try:
- 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.
- Make sure your data model is optimized, including using the appropriate data types, removing unnecessary columns, creating indexes, and more.
- Try using filters or queries in Power Query Editor to reduce the amount of data that needs to be calculated.
- 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.
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
Since one of the value used for sumx is dynamically calculated , doing SUMX using calculated column will give wrong result.
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
I need to calculate say col 1 * cogs.
Contribution= Cogs / sum of cogs ( for selected slicers in the report)
If I do SUMX of col 1 * contribution, then contribution will aggregate my value , it won't give me the value as per slicer selection