Cumulative Sum Performance issue

[alexa_0028]

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?

[johnt75]

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]

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]

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.