Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
CarlosOlmos29
Helper I
Helper I

Column accumulated by categories in power query

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

1 ACCEPTED SOLUTION
v-jingzhan-msft
Community Support
Community Support

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"

vjingzhanmsft_0-1714377616522.png

vjingzhanmsft_1-1714377945923.png

 

@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!

View solution in original post

2 REPLIES 2
v-jingzhan-msft
Community Support
Community Support

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"

vjingzhanmsft_0-1714377616522.png

vjingzhanmsft_1-1714377945923.png

 

@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
Super User
Super User

Hi @CarlosOlmos29 

 

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

 

Regards

 

Phil



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.