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
Anonymous
Not applicable

Cumulative sum in Power query editor

Hello all,

 

How I can do the cumulative sum in power query editior as shown below

 

KarthikKV_0-1620618677264.png

 

I need to calculate the 3rd column as shown above.

 

Thanks,

Karthik

2 ACCEPTED SOLUTIONS
amitchandak
Super User
Super User

v-cazheng-msft
Community Support
Community Support

Hi @Anonymous 

You can try these steps.

 

Add Index to your query and create a Custom column

 

v-cazheng-msft_0-1620789460334.png

 

Or you can use following M query in the Advanced Editor:

let

    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8ssv0zUyMDJQ0lEyM7LUMzdVitWJVnJJTYaJGhrpGZuDBb0S80CChkBBYwtLPRNjsKhbahJM1NDAzFTPwgQs7JtYBBc2N9EzApobCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [YearMonth = _t, Amount = _t]),

    #"Added Index" = Table.AddIndexColumn(Source, "Index", 1, 1, Int64.Type),

    #"Changed Type" = Table.TransformColumnTypes(#"Added Index",{{"Amount", type number}}),

    #"Added Custom" = Table.AddColumn(#"Changed Type", "Running Total", each List.Sum(List.FirstN(#"Changed Type"[Amount],[Index])))

in

#"Added Custom"

 

The result looks like this:

v-cazheng-msft_1-1620789460337.png

 

For more details, you can refer the attached pbix file. For further understanding running total in Power Query, you can refer these links:

Quickly Create Running Totals in Power Query

Grouped running total with Power Query M

 

Best Regards

Caiyun Zheng

 

If this post helps, please consider make it as the solution by Accept it as Solution. Really appreciate!

View solution in original post

9 REPLIES 9
Rickmaurinus
Helper V
Helper V

A bit late to the party, you may enjoy reading these articles. With increasing complexity, here's how to: 

- Create running totals (4 methods): https://gorilla.bi/power-query/running-total/

- Apply running total by category: https://gorilla.bi/power-query/running-total-by-category/

- Create running totals in bulk: https://gorilla.bi/power-query/creating-multiple-running-totals/

 

The list.generate versions are fast. List.Accumulate helps in creating multiple columns. And there's a smart method to combine the tables again. 

 

Hope it helps,

Rick

 

Thanks, good suggestion! I ended up replacing the `List.Accumulate()` step with the one-liner `=List.Accumulate(Source[Amount], {}, (state,current) => state & {List.Last(state, 0) + current})`, combined with your method for adding the whole column to the table at once.

rodrigochaves
Regular Visitor

@amitchandakI followed your blog solution and it was awesome! Thanks for sharing your knowledge!

v-cazheng-msft
Community Support
Community Support

Hi @Anonymous 

You can try these steps.

 

Add Index to your query and create a Custom column

 

v-cazheng-msft_0-1620789460334.png

 

Or you can use following M query in the Advanced Editor:

let

    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8ssv0zUyMDJQ0lEyM7LUMzdVitWJVnJJTYaJGhrpGZuDBb0S80CChkBBYwtLPRNjsKhbahJM1NDAzFTPwgQs7JtYBBc2N9EzApobCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [YearMonth = _t, Amount = _t]),

    #"Added Index" = Table.AddIndexColumn(Source, "Index", 1, 1, Int64.Type),

    #"Changed Type" = Table.TransformColumnTypes(#"Added Index",{{"Amount", type number}}),

    #"Added Custom" = Table.AddColumn(#"Changed Type", "Running Total", each List.Sum(List.FirstN(#"Changed Type"[Amount],[Index])))

in

#"Added Custom"

 

The result looks like this:

v-cazheng-msft_1-1620789460337.png

 

For more details, you can refer the attached pbix file. For further understanding running total in Power Query, you can refer these links:

Quickly Create Running Totals in Power Query

Grouped running total with Power Query M

 

Best Regards

Caiyun Zheng

 

If this post helps, please consider make it as the solution by Accept it as Solution. Really appreciate!

Anonymous
Not applicable

Thank you so much @v-cazheng-msft . I somehow resolved it in a different way but will try your way next time. Is there a guide where I can start understanding M code from scratch? 

@Anonymous 

You can refer these official documents Power Query M formula language, it may help you.

 

Best Regards

Caiyun Zheng

Anonymous
Not applicable

Thank you so much

amitchandak
Super User
Super User

Thanks for the solution but here my index again start at 0 when the year change. What should be m query formula then? 

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.