Forum Discussion

Thigs's avatar
Thigs
Helper IV
4 years ago
Solved

Calculated Column Running Total

Hi all, 

I've looked at several other posts answering similar questions but can't find exactly what I need. Basically, I have a column for Index (starting at 1), and a column for quantity of items. I want to do a running total of the quantity based on the Index column - but it needs to be in a calculated column rather than a measure as I need to use the column itsself for an additional analysis in a bit. I have tried the following, but it doesn't work - 

 

Cumulative Total =
CALCULATE (
SUM('Data'[QtyPicked]),
FILTER (
ALL( 'Data'[Index] ),
'Data'[Index] <= MAX ( 'Data'[Index] )
)
)
 
It seems to just be giving me the quantity, not the running quantity
 
I also tried something like this - 
Running Total = if('Data'[Index] = 1, 'Data'[QtyPicked],
CALCULATE(sum('Data'[QtyPicked]),
FILTER( 'Data'[Index] <= MAX('Data'[Index]))))
 
Which also gave crazy numbers, so no good either. 
  • tamerj1's avatar
    tamerj1
    4 years ago

    Hi Thigs 

    for a calculated column you can use 

    Cumulative Total =
SUMX (
    FILTER ( 'Data', 'Data'[Index] <= EARLIER ( 'Data'[Index] ) ),
    'Data'[QtyPicked]
)

1 Reply

  • tamerj1's avatar
    tamerj1
    Community Champion
    4 years ago

    Hi Thigs 

    for a calculated column you can use 

    Cumulative Total =
SUMX (
    FILTER ( 'Data', 'Data'[Index] <= EARLIER ( 'Data'[Index] ) ),
    'Data'[QtyPicked]
)