Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Insufficient memory using Earlier

Hi everyone.

 

So I have this sample data.

 

Table 'Sales'

 

Rank and Running Sales are calculated columns using the following formulas.

Rank = RANKX( Sales , Sales[Sales] )
Running Sales = CALCULATE( SUM( Sales[Sales] ) , ALL( Sales ) , Sales[Rank] <= EARLIER( Sales[Rank] ) )
 
This works without a problem for small samples, but I'm working with a real Table of approximately 100k rows.
I'm assuming the need for an excessive ammount of processing comes from the EARLIER function, is there any alternative to obtain the same result with large sample sizes. Or what alternatives could work?
 
Thanks in advance.
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    3 years ago

    Anonymous Couple things to try:

    Running Sales Column = 
  VAR __Rank = [Rank]
  VAR __Result = 
    CALCULATE( 
      SUM( Sales[Sales] ) , ALL( Sales ) , Sales[Rank] <= __Rank
    )
RETURN
  __Result


Running Sales Colum 2 = 
  VAR __Rank = [Rank]
  VAR __Result = 
    SUMX( 
      FILTER( ALL( Sales ) , Sales[Rank] <= __Rank),
      [Sales]
    )
RETURN
  __Result

3 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    3 years ago

    Anonymous Couple things to try:

    Running Sales Column = 
  VAR __Rank = [Rank]
  VAR __Result = 
    CALCULATE( 
      SUM( Sales[Sales] ) , ALL( Sales ) , Sales[Rank] <= __Rank
    )
RETURN
  __Result


Running Sales Colum 2 = 
  VAR __Rank = [Rank]
  VAR __Result = 
    SUMX( 
      FILTER( ALL( Sales ) , Sales[Rank] <= __Rank),
      [Sales]
    )
RETURN
  __Result
    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Thanks! Greg_Deckler Second option (with SUMX) worked like a charm, the others did not? Any idea why?

      • Greg_Deckler's avatar
        Greg_Deckler
        Community Champion
        3 years ago

        Anonymous CALCULATE isn't really all that great with single table data models.