Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
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
Solved! Go to Solution.
@Anonymous , refer this blog for power query running total
https://www.myonlinetraininghub.com/quickly-create-running-totals-in-power-query
Hi @Anonymous
You can try these steps.
Add Index to your query and create a Custom column
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:
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!
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.
@amitchandakI followed your blog solution and it was awesome! Thanks for sharing your knowledge!
Hi @Anonymous
You can try these steps.
Add Index to your query and create a Custom column
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:
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!
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
Thank you so much
@Anonymous , refer this blog for power query running total
https://www.myonlinetraininghub.com/quickly-create-running-totals-in-power-query
Thanks for the solution but here my index again start at 0 when the year change. What should be m query formula then?
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
76 | |
72 | |
56 | |
38 | |
35 |
User | Count |
---|---|
83 | |
68 | |
59 | |
46 | |
45 |