Column accumulated by categories in power query

[CarlosOlmos29]

Hello power BI community.

I am trying to create an accumulated column in power query. In another post I found this formula to accumulate:

List.Sum(List.Range(#“Added Index”[Actual],0,[Index]))

This formula works when the list is continuous, that is, there is only one category of data, but I have several, so I need the accumulated to restart when changing categories. I appreciate if you can help me from the above formula or with a new one.

Thank you very much in advance for your support

[SuperM1973]

Hello, I´m trying to understand the sintaxe List.Sum(List.Range(#“Added Index”[Actual],0,[Index]))!

Could you please explain me why we put the parametr [Index]?

Thanks

[Anonymous]

[Index] is a column named "Index". You can create it with feature: Add an index column.

#“Added Index” is the step to add the "Index" column, also representing the table result after this step.

#“Added Index”[Actual] represents the [Actual] column from the #“Added Index” table. It is a list. 

List.Range extracts the list values from the first one to the [Index] one ( [Index] is the number of list items to be extracted ). 

List.Sum calculates the sum of extracted list values. 

[Anonymous · Accepted solution]

Hi @CarlosOlmos29 

Here is a solution by using your formula. The logic is to group the table by category column and add an Index column to each group table first. Then add a column to each group table with your formula to get the running total within a group. Finally expand the group table column. 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSgQCJR0lQ6VYHfI4SUlJQLYR6exYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Category = _t, Actual = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Category", type text}, {"Actual", Int64.Type}}),
    
    // add the following steps
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Category"}, {{"GroupData", each let actualValues = _[Actual] in Table.AddColumn(Table.AddIndexColumn(_, "Index", 1, 1), "RunningTotal", each List.Sum(List.Range(actualValues,0,[Index])))}}),
    #"Expanded GroupData" = Table.ExpandTableColumn(#"Grouped Rows", "GroupData", {"Actual", "Index", "RunningTotal"}, {"Actual", "Index", "RunningTotal"})
in
    #"Expanded GroupData"

@PhilipTreacy 's blog link shares another solution. You can have a try!

Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!

[PhilipTreacy]

Hi @CarlosOlmos29 

Check this article for how to create Grouped Running Totals in Power Query

Regards

Phil