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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
carlacervonif
Frequent Visitor

Running Total or Cumulative Total column in Power Query

Please please PLEASE make it possible to create a running/cumulative total column in Power Query. The amount of hoops I have to go through to get something as simple as a cumulative value column is crazy. Power BI is able to do things that are ALOT more complex than a running/cumulative total column so I'm confused as to why it has to be so difficult. Even after searching for solutions for HOURS, I have not found a solution for a running/cumulative total column in Power Query without having to copy and/or group a table.

2 REPLIES 2
BA_Pete
Super User
Super User

Hi @carlacervonif ,

 

The reason this topic doesn't seem to get discussed/solved very often is because Power Query isn't really designed for this type of operation. It's a transformation/mashup tool. DAX within a data model is designed to make calculations such as this very fast and efficient.

 

That being said, it is possible. The trick with doing it in Power Query is doing it with a single table scan, otherwise it can perform very poorly over larger datasets. Here's a good reference to get you started, but it may depend on your specific data structure and needs:
https://gorilla.bi/power-query/running-total/#fast-running-totals 

 

Pete



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




zenisekd
Super User
Super User

Hi, 
a) if you want to make a suggestion, I suggest you post it to Ideas, where it gets votes and better highlight for Microsoft. https://ideas.fabric.microsoft.com/
b)

To create a cumulative total in Power Query, you can follow these steps:

  1. Load your data into Power Query: Import your data into Power Query from your data source. This could be from an Excel table, CSV file, database, or other sources.

  2. Add an Index Column: Go to the "Add Column" tab in the Power Query Editor, then select "Index Column" and choose "From 0". This will add a new column with a sequential index starting from 0.

  3. Sort the data: Sort your data by the column you want to create the cumulative total for. You can do this by clicking on the column header and selecting "Sort Ascending" or "Sort Descending" as appropriate.

  4. Add a Custom Column for the Cumulative Total:

    • Go to the "Add Column" tab.
    • Click on "Custom Column".
    • In the formula bar that appears, enter a formula to calculate the cumulative total.
    • For example, if your column containing the values you want to cumulate is named "Value", you can use the following formula: = List.Sum(List.FirstN([Value], [Index] + 1))
    • Click OK.
  5. Expand the new column: Click the expand button (double-headed arrow) on the header of the newly created custom column. Uncheck the "Use original column name as prefix" option if you don't want the original column name to be added as a prefix.

There you go. 

Kudos and mark as solution appreciated 🙂

 

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

FebPBI_Carousel

Power BI Monthly Update - February 2025

Check out the February 2025 Power BI update to learn about new features.

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Feb2025 NL Carousel

Fabric Community Update - February 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors
Top Kudoed Authors