cancel
Showing results 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

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

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

Thanks,

Karthik

2 ACCEPTED SOLUTIONS
Super User
Community Support

Hi @Anonymous

You can try these steps.

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]),

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

in

The result looks like this:

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!

9 REPLIES 9
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.

Regular Visitor

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

Community Support

Hi @Anonymous

You can try these steps.

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]),

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

in

The result looks like this:

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?

Community Support

@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

Super User
New Member

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

Announcements

#### 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 Monthly Update - June 2024

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

#### Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors