Forum Discussion

Mann's avatar
Mann
Resolver 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!!

Mann's avatar
Mann
Resolver 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
Resolver 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!