How to achieve running total with power query?

Question

I want to do a running total with power query like I did with Tableau software before. Does anyone have ideas, thanks in advance!

It would be very helpful to indicate that this is a question of recreating a Tableau style calculation in Excel. Also, providing sample data for reproducibility will go a long way to getting assistance. — David F. Severski, Jun 29 '15 at 14:57

score 7 · Answer 1 · answered Dec 18 '15 at 03:51

Apologies for the very late answer - this challenge has been nagging at me for many months.

There are few solutions floating around forums and blogs but they all seem to need pages of custom M code. Some also cant meet the common requirement of needing to restart the running total when a group changes.

So I came up a with a technique you can build without writing any custom code - you just click around in the Power Query window and add one very simple Custom Column.

The key steps are:

Add an Index column
Group By the column(s) that define your groups, and add an "All Rows" column
Duplicate that "All Rows" column
Expand both the original and copy of the "All Rows" column (at this point you have a "cross-product" result of every combination of rows, within each group)
Add a Custom Column "Cumulative" to determine which of the combination rows to include in the Running Total, e.g. [Index] >= [#"All Rows - Copy.Index"]
Filter on the Custom Column "Cumulative" = TRUE
Group By the original columns and Sum the copy of the target field

I built a working solution which you can download from my OneDrive and try out:

http://1drv.ms/1AzPAZp

It's the file: Power Query demo - Running Total

score 4 · Answer 2 · edited Jul 10 '19 at 23:14

4

Very easy.

Step 1: Add an index

#"Added Index" = Table.AddIndexColumn(#"**Prior Step**", "Index", 0, 1)

Step 2: Add a running total

#"Added Running Total" = Table.AddColumn(#"Added Index", "Running Total, each List.Sum(List.FirstN(#"Added Index"[**Column to Add Values From**],[Index]+1)))

edited Jul 10 '19 at 23:14

Anthony

3,595
2
29
38

answered Apr 13 '18 at 02:23

Adam

41
1

1

As @Martina mentioned, you need to close the quotation mark for `Running Total`. – Anthony Jul 10 '19 at 23:13

score 2 · Answer 3 · answered Dec 09 '16 at 05:21

Using Excel 2016,

Add Index named "RunningTotal"
Go to Query -> Edit -> Add Reference Query (Index must be a column within Source**
Go to View -> Advanced Editor and paste the below code to create a function:
Rename the query to fnRunningTotal

= (tbl as table, sumcolumn as text, rowindex as number) =>
let
    #"Removed Other Columns" = Table.SelectColumns(tbl,{sumcolumn, "RunningTotal"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Other Columns", each [RunningTotal] <= rowindex),
    #"Renamed Columns" = Table.RenameColumns(#"Filtered Rows",{{sumcolumn, "Temp"}}),
    #"Grouped Rows" = Table.Group(#"Renamed Columns", {}, {{"FinalRunningTotal", each List.Sum([Temp]), type number}}),
    FinalRunningTotal = Record.Field(#"Grouped Rows"{0},"FinalRunningTotal")
in
    FinalRunningTotal

To use the function:

= Table.AddColumn(Source, "RunningTotalAmount", each fnRunningTotal(Source,"MarketValue",[RunningTotal]))

score 2 · Answer 4 · edited May 14 '19 at 07:09

2

I mean this code from Adam works but with adding " behind "Running Total:

Step 1: Add an index

"Added Index" = Table.AddIndexColumn(#"Prior Step", "Index", 0, 1)

Step 2: Add a running total

"Added Running Total" = Table.AddColumn(#"Added Index", "Running Total", each List.Sum(List.FirstN(#"Added Index"[Column to Add Values From],[Index]+1)))

edited May 14 '19 at 07:09

c1au61o_HH

867
7
14

answered May 14 '19 at 03:44

Martina

21
1

Rory · Answer 5 · 2015-06-30T13:18:42.813

1

I think if it is possible in Power Query it would be very complicated. It's just the wrong tool. Use Power Query to bring the data into Power Pivot and create the running total there instead. It is a standard pattern. See this excellent guide: http://www.daxpatterns.com/cumulative-total/

edited Jun 30 '15 at 13:18

answered Jun 30 '15 at 12:55

Rory

959
10
22

Thanks@Rory, actually I need to build some dashboards with powerview and publish them to powerbi preview, I couldn't add a data refresh if I use powerpivot, that was why I stuck to Power query even if I was totally agree with you. – Summer_Solstice Jun 30 '15 at 16:15
You create a Power Pivot model using Power Query to load your data. Then you upload the workbook to Power BI Preview and it becomes another dataset. You can refresh so long as your Power Query source is supported https://support.powerbi.com/knowledgebase/articles/474669-refresh-data – Rory Jun 30 '15 at 21:19

score 1 · Answer 6 · answered Oct 07 '17 at 14:31

I believe the best technique so far is creating a list and then creating another column using List.Sum. This does not require any complicated codes. Just point and click and will solve the issue.

More details: https://www.sumproduct.com/blog/article/power-query-one-route-to-a-running-total

score 1 · Answer 7 · answered Mar 09 '21 at 13:39

My favorites

Cumulative on single column, no grouping

#"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1),
#"Added Cum Total" = Table.AddColumn(#"Added Index", "CumTotal", each List.Sum(List.FirstN(#"Added Index"[ColumnNameDoingCumulative],[Index]+1))),

Cumulative on a single column, multiple grouping

#"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1),
#"Added Custom" = Table.AddColumn(#"Added Index","CumTotal",(i)=>List.Sum(Table.SelectRows(#"Added Index", each [Group1Column]=i[Group1Column] and [Group2Column]=i[Group2Column] and [Index]<=i[Index]) [ColumnNameDoingCumulative]), type number ),

How to achieve running total with power query?

7 Answers7

Linked