Forum Discussion

alexa_0028's avatar
alexa_0028
Resolver II
4 years ago

Cumulative Sum Performance issue

Hi All, I have a formula to calculate cumulative sum which gives the results but it has extremely slow performance : Cumulative at country = VAR AccountRev = FinalTraumaAllocation[Act22] var ...
Need Help
johnt75's avatar
johnt75
Super User
4 years ago

It is not best practice to use SUMMARIZE to add calculated columns, see the article at https://www.sqlbi.com/articles/best-practices-using-summarize-and-addcolumns/ 

Try instead

Cumulative at country =
VAR AccountRev = FinalTraumaAllocation[Act22]
var table1 = ADDCOLUMNS(
    SUMMARIZE(ALLSELECTED('Comp Org'[Comp. Org Country]),'Comp Org'[Comp. Org Country]), 
    "Sales22", CALCULATE( FinalTraumaAllocation[Act22] )
)
Return 
Sumx(FILTER(table1, [Sales22]>=AccountRev), [Sales22])
  • alexa_0028's avatar
    alexa_0028
    Resolver II
    4 years ago

    Hi johnt75 
    Thank you for the suggestion. I have reformatted DASX.
    However, I still have performance issues. It is loading visual in 1 minute.

    • johnt75's avatar
      johnt75
      Super User
      4 years ago

      If the cumulative totals don't need to take account of other filter settings, such as date range, then you could look into moving the calculations into Power Query or creating a summary calculated table in DAX. That way the performance hit would be a one-off during data load and the visuals should be instantaneous.

      If you do need to interact with other slicers or filters I am afraid I cannot help further, optimization is not my strong suit.