How to acumulate values of a column

[Anonymous]

Hi,

I have a coulmn with decimal values, and I want to create a calculated column that will be the cumulative of that column. For example: 

Column 1   cumulative

1,65                1,65

1,65                3,3

0,33                3,63

0,66                4,29

Any tips on how to accomplish this? Thanks!

[v-shex-msft · Accepted solution]

Hi @Anonymous,

If your table contains index column, it is possible to get the running total based on index column.

Sample: calculate column

Running Total = 
SUMX (
    FILTER ( ALL ( Test ), Test[Index] <= EARLIER ( Test[Index] ) ),
    [Value]
)

Regards,

Xiaoxin Sheng

[MarcelBeug]

A Power Query solution would be:

let
    #"**** QUERY SUMMARY ***" =
        "This query adds a column with the accumulation of the value in column ""Column 1"".#(lf)" &
        "A buffered list is created from the values.#(lf)" &
        "Using List.Accumulate, the values are accumulated.#(lf)" &
        "The initial value (the ""seed"" parameter of List.Accumulate) is a list with 1 item: {0}#(lf)" &
        "With each iteration, the value (v) is added to the last item of the cumulated list (c) and#(lf)" &
        "the result is concatenated to the cumulated list.#(lf)" &
        "After accumulation, the first element {0} is skipped.#(lf)" &
        "The resulting list is added to the table by transforming the table into columns (a list of lists),#(lf)" &
        "the list of accumulated values is added to this list of lists,#(lf)" &
        "the result is transformed to a table (using Table.FromColumns).#(lf)" &
        "This table gets the table type from the Source Table, to which the column ""Cumulative"" is added with dummy value 1:#(lf)" &
        "that additional column is only required for the column name and column type.",
    Source = Table1,
    NumberList = List.Buffer(Source[Column 1]),
    Cumulative = List.Skip(List.Accumulate(NumberList,{0},(c,v) => c & {(List.Last(c)+v)})),
    AddCumulativeToSource = 
        Table.FromColumns(Table.ToColumns(Source)&{Cumulative},
                          Value.Type(Table.AddColumn(Source,"Cumulative", each 1, type number)))
in
    AddCumulativeToSource

[Anonymous]

Thank you, @MarcelBeug. I'm still learning power query language, so your code is a bit difficult for me, but thanks.