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 table1 = SUMMARIZE(ALLSELECTED('Comp Org'[Comp. Org Country]),'Comp Org'[Comp. Org Country], "Sales22", FinalTraumaAllocation[Act22])
Return 
Sumx(FILTER(table1, [Sales22]>=AccountRev), [Sales22])

Can someone please help to optimise formula?
 
Need Help

3 Replies

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