Forum Discussion

Mann's avatar
Mann
Icon for Resolver III rankResolver III
7 years ago
Solved

Slow running Cumulative total calculation for Calculated Column

Hi Guys,   I created a calculated column calculation to find the running total. Here is the data set. I have Dates, Individual IDs, Logic1 (It is a calculated column which find the breakpoints for...
Need Help
  • Mann's avatar
    Mann
    7 years ago

    Anonymous 

     

    Thanks to your suggestion! I have changed the calculation to:

    Running Total =
CALCULATE (
    SUM ( 'Table2'[Logic 1] ),
    FILTER (
        'Table2',
        AND (
            'Table2'[Individual ID]
                = EARLIER ( 'Table2'[Individual ID] ),
            'Table2'[Index]
                <= EARLIER ( 'Table2'[Index] )
        )
    )
)

    And the calculation of Logic 2 to:

    Logic 2 =
RANKX (
    FILTER (
        'Table2',
        AND('Table2'[Running Total] = EARLIER ( 'Table2'[Running Total]) ,
            'Table2'[Individual ID] = EARLIER('Table2'[Individual ID]))
    ),
    'Table2'[Index],
    ,
    ASC
)

    Now the columns is getting calculation in less than 1 min than 1hour before.

     

    Thanks!!

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Mann - 

The problem is with iterations. Picture row #2M. First, it checks whether its index is smaller. Then it takes those 2M rows and adds them together.

 

Is there any way to break this down by some attribute? That would reduce the number of rows it checks dramatically, first because it would only check rows within that category, and secondly it would have less rows to add up.

 

Hope this helps,

Nathan

Mann's avatar
Mann
Icon for Resolver III rankResolver III
7 years ago

Hi Nathan,

 

Thanks for the reply.

I think we can break it by Individual IDs as a category. I mean if it is scanning the complete table then passing filter conditon on Individual Ids would also give my final result i.e. Run the cumulative total per individual ID.

 

Also, I am calculating running total because I want to RANK every set of consecutive days per Ind Id later. Currently the calculation for that is (third calculated column):

Logic 2 =
RANKX (
    FILTER (
        Table2,
        Table2[Running Total] = EARLIER ( Table2[Running Total] )
    ),
    Table2[Index],
    ,
    ASC
)

This si giving expected result as per earlier calculation. If I change the calculation for "Running Total" as per your advice then I might need to change above calculation too. I am fine with it.

 

Just struggling to get it done.

 

Here is the final outcome coming as per current calculations:

  • Mann's avatar
    Mann
    Icon for Resolver III rankResolver III
    7 years ago

    Anonymous 

     

    Thanks to your suggestion! I have changed the calculation to:

    Running Total =
CALCULATE (
    SUM ( 'Table2'[Logic 1] ),
    FILTER (
        'Table2',
        AND (
            'Table2'[Individual ID]
                = EARLIER ( 'Table2'[Individual ID] ),
            'Table2'[Index]
                <= EARLIER ( 'Table2'[Index] )
        )
    )
)

    And the calculation of Logic 2 to:

    Logic 2 =
RANKX (
    FILTER (
        'Table2',
        AND('Table2'[Running Total] = EARLIER ( 'Table2'[Running Total]) ,
            'Table2'[Individual ID] = EARLIER('Table2'[Individual ID]))
    ),
    'Table2'[Index],
    ,
    ASC
)

    Now the columns is getting calculation in less than 1 min than 1hour before.

     

    Thanks!!

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Mann - Great to hear!