# Runnig total in power Query . • ### Question

• I need to calculate running total in power query based on a field  "Final Time" for which "Lot Id" field has same values.When the Lot Id changes , the running total should start from the scratch. Can anyone help to solve this. I should get the running total as stated in below example.

Lot Id      Final Time   Running Total

1               0.5              0.5

1               1.2              1.7

1                0.7             1.9

2                0.3             0.3

2                0.5             0.8

Tuesday, June 9, 2015 5:29 AM

• Some variation needed to apply it on LotId-Level (Product on my example below):

let
// For the general principle see: http://www.excelguru.ca/blog/2015/03/31/create-running-totals-in-power-query/
Source = Excel.CurrentWorkbook(){[Name="Sales"]}[Content],
// Variation needed for applying it on ProductID-level
Grouped = Table.Group(Source, {"Product"}, {{"Count", each Table.RowCount(_), type number}}),
RT = (Prod)=>
let
Filter=Table.SelectRows(Source, each [Product] = (Prod)),
in
RT,
RunningTotal = Table.AddColumn(Grouped, "RT", each RT([Product])),
Expand = Table.ExpandTableColumn(RunningTotal, "RT", {"Date", "Product", "Sale", "Indeks", "Running Total"}, {"Date", "Product.1", "Sale", "Indeks", "Running Total"})
in
Expand

Imke

Tuesday, June 9, 2015 8:26 AM
• Hi :-)

Sorry Gill but it is not good way (efficiency is very poor) :-(

Try this one below. You can choose function you want to use (fxRunningTotal or fxRunningTotalLG) and check the difference.

```let
// function based on List.Sum(List.FirstN
fxRunningTotal = (tab as table, col_name as text) as table =>

let
RemCol = Table.RemoveColumns(RunningTotal,{"Indeks"})
in
RemCol,
// function based on List.Generate
fxRunningTotalLG = (tab as table, col_name as text) =>

let
LTS = List.Buffer(Table.Column(tab, col_name)),
ListCount = List.Count(LTS),
LstGen = List.Generate(()=>
[Counter=1, RT= LTS{0}],
each [Counter]<=ListCount,
each [Counter=[Counter]+1, RT=([RT]+LTS{[Counter]})],
each [RT]),
in
TblToCol,

// choose column to grouping and to sum
CFT = "Lot Id", // column name for grouping
CFRT = "Final Time", // column name to sum

Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
GrupCol = Table.Group(Source, {CFT}, {{"tab", each _, type table}}),
RemCol = Table.SelectColumns(GrupCol,{"tab"}),
// You can choose function fxRunningTotalLG or fxRunningTotal - arguments are the same
TransfCol = Table.TransformColumns(RemCol, {"tab", each fxRunningTotalLG(_, CFRT)}),
ExpCol = Table.ExpandTableColumn(TransfCol, "tab", {"Lot Id", "Final Time", "Running Total"}, {"Lot Id", "Final Time", "Running Total"})
in
ExpCol
```
Regards :-)

Tuesday, June 9, 2015 2:01 PM

### All replies

• Here is a recommended blog post by Ken Puls that can help you to calculate it with Power Query.
Tuesday, June 9, 2015 7:34 AM
• Some variation needed to apply it on LotId-Level (Product on my example below):

let
// For the general principle see: http://www.excelguru.ca/blog/2015/03/31/create-running-totals-in-power-query/
Source = Excel.CurrentWorkbook(){[Name="Sales"]}[Content],
// Variation needed for applying it on ProductID-level
Grouped = Table.Group(Source, {"Product"}, {{"Count", each Table.RowCount(_), type number}}),
RT = (Prod)=>
let
Filter=Table.SelectRows(Source, each [Product] = (Prod)),
in
RT,
RunningTotal = Table.AddColumn(Grouped, "RT", each RT([Product])),
Expand = Table.ExpandTableColumn(RunningTotal, "RT", {"Date", "Product", "Sale", "Indeks", "Running Total"}, {"Date", "Product.1", "Sale", "Indeks", "Running Total"})
in
Expand

Imke

Tuesday, June 9, 2015 8:26 AM
• In here is my version (inspired from the same source:  http://www.excelguru.ca/blog/2015/03/31/create-running-totals-in-power-query/).

Assume that in your Excel worksheet Table1 contains "Lot Id" and "Final Time" as above.

First you should create the following function query, and name it "GetRunningTotal".

```(source as table, id as number, rowNum) =>
let
#"Changed Type" = Table.TransformColumnTypes(source,{{"Lot Id", Int64.Type}, {"Final Time", type number}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Lot Id] = id)),
#"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each [Index] <= rowNum),
#"Grouped Rows" = Table.Group(#"Filtered Rows1", {"Lot Id"}, {{"RunningTotal", each List.Sum([Final Time]), type number}}),
RunningTotal = #"Grouped Rows"{[#"Lot Id"=id]}[RunningTotal]
in
RunningTotal```
Next - Create the following query -
```let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Lot Id", Int64.Type}, {"Final Time", type number}}),
in
#"Removed Columns"```

Imke, Please let me know if my query is less efficient :)

Tuesday, June 9, 2015 10:50 AM
• Thanks Imke .. Its working perfectly now!!!!

Tuesday, June 9, 2015 12:44 PM
• Hi :-)

Sorry Gill but it is not good way (efficiency is very poor) :-(

Try this one below. You can choose function you want to use (fxRunningTotal or fxRunningTotalLG) and check the difference.

```let
// function based on List.Sum(List.FirstN
fxRunningTotal = (tab as table, col_name as text) as table =>

let
RemCol = Table.RemoveColumns(RunningTotal,{"Indeks"})
in
RemCol,
// function based on List.Generate
fxRunningTotalLG = (tab as table, col_name as text) =>

let
LTS = List.Buffer(Table.Column(tab, col_name)),
ListCount = List.Count(LTS),
LstGen = List.Generate(()=>
[Counter=1, RT= LTS{0}],
each [Counter]<=ListCount,
each [Counter=[Counter]+1, RT=([RT]+LTS{[Counter]})],
each [RT]),
in
TblToCol,

// choose column to grouping and to sum
CFT = "Lot Id", // column name for grouping
CFRT = "Final Time", // column name to sum

Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
GrupCol = Table.Group(Source, {CFT}, {{"tab", each _, type table}}),
RemCol = Table.SelectColumns(GrupCol,{"tab"}),
// You can choose function fxRunningTotalLG or fxRunningTotal - arguments are the same
TransfCol = Table.TransformColumns(RemCol, {"tab", each fxRunningTotalLG(_, CFRT)}),
ExpCol = Table.ExpandTableColumn(TransfCol, "tab", {"Lot Id", "Final Time", "Running Total"}, {"Lot Id", "Final Time", "Running Total"})
in
ExpCol
```
Regards :-)

Tuesday, June 9, 2015 2:01 PM
• Bill, you're genius!

How smart to split up the Final Time values & feed them as a table into the function - no need to filter anything then!

(... & thanks for setting up the racetrack :-))

Imke

Tuesday, June 9, 2015 3:45 PM
• Thanks Bill , as mentioned By Imke , now I doesnt need to apply any filters

Wednesday, June 10, 2015 10:32 AM
• Thanks Imke for your kind words but i'm an ordinary guy only :-))

I think, List.Generate method is better than List.First method in most cases.
Especially if we have many thousand rows of data and a few subcategories only.
In this case, efficiency is completely different.

sq :-)

Bill

Thursday, June 11, 2015 2:24 PM
• You are welcome :-)
Thursday, June 11, 2015 2:25 PM
• Hello Imke,now that we have the running total, we could perform an ABC analysis for each product. I tried to do that, but I got stuck firguring out how to calculate the grandTotal for each product type. I can get the grand total for the full list of product, but I am not able to get the grand total relative to each product. Can you please help?

Sunday, July 5, 2015 9:57 AM
• Hi Diego,

Did you try Bill's query expression above? It should work for you. You just need to replace "Lot Id" with your Product type column name.

If it doesn't work, please share the table schema that you have, and we can edit the expression above to work on your table.

Thank you,

Gil

Sunday, July 5, 2015 10:02 AM
• Hi Diego,

is my understanding right, that you want to apply the technique to a table where you not only have the product but also an additional grouping Level/column for the products within whom you want to perform the running totals (product type)?

Then you just need to pass the product type as a third field into the function and use it there to filter the source table (tab). Please tell if you need the code for that.

Imke

Sunday, July 5, 2015 12:55 PM
• Well, and the GrandTotal for each product type would be a group on product type only with SUM on CFRT (you could self-join that on ProductGroup in your ABC-Analysis).

Imke

Sunday, July 5, 2015 1:04 PM
• Hi Diego,

here comes the code based on this example, where you have to aggregate on product first, because we have multiple lines per product.

let
fxRunningTotalLG = (tab as table, col_name as text, ProdGroup) =>

let
LTS = List.Buffer(Table.Column(Table.SelectRows(tab, each [ProductGroup] = ProdGroup), col_name)),
ListCount = List.Count(LTS),
LstGen = List.Generate(()=>
[Counter=1, RT= LTS{0}],
each [Counter]<=ListCount,
each [Counter=[Counter]+1, RT=([RT]+LTS{[Counter]})],
each [RT]),
in
TblToCol,

// choose column to grouping and to sum
CFT = "Product", // column name for grouping
CFRT = "Sale", // column name to sum

Source = Excel.CurrentWorkbook(){[Name="SalesProductGroup"]}[Content],
SubtotalProductGroup = Table.Group(Source, {"ProductGroup"}, {{"TotalProductGroup", each List.Sum([Sale]), type number}}),
GroupedRows = Table.Group(Source, {"Product", "ProductGroup"}, {{"Sale", each List.Sum([Sale]), type number}}),
SortedRows = Table.Sort(GroupedRows,{{"ProductGroup", Order.Ascending}, {"Sale", Order.Descending}}),
Reorder = Table.ReorderColumns(SortedRows,{"Sale", "ProductGroup", "Product"}),
Group = Table.Buffer(Table.Group(Reorder, {"ProductGroup"}, {{"tab", each _, type table}})),
TransfCol = Table.AddColumn(Group, "Result", each fxRunningTotalLG(_[tab], CFRT, [ProductGroup])),
RemOther = Table.SelectColumns(TransfCol,{"Result"}),
ExpResult = Table.ExpandTableColumn(RemOther, "Result", {"Product", "Sale", "ProductGroup", "Running Total"}, {"Product", "Sale", "ProductGroup", "Running Total"}),
MergeSubtotals = Table.NestedJoin(ExpResult,{"ProductGroup"},SubtotalProductGroup,{"ProductGroup"},"NewColumn"),
ExpandSubtotals = Table.ExpandTableColumn(MergeSubtotals, "NewColumn", {"TotalProductGroup"}, {"TotalProductGroup"}),
Percentage = Table.AddColumn(ExpandSubtotals, "Percentage", each [Running Total]/[TotalProductGroup]),
Classification = Table.AddColumn(Percentage, "Classification", each if [Percentage] < 0.7 then "A" else if [Percentage] < 0.9 then "B" else "C")
in
Classification

Imke

Sunday, July 5, 2015 8:35 PM
• Ouch, I've spoiled Bill's clever solution - here comes the tuned code - at the end it's like Gil said: Your Product group will be the new Lot ID (just that we need some aggregation and sorting before as well :-):

let
fxRunningTotalLG = (tab as table, col_name as text) =>

let
LTS = List.Buffer(Table.Column(tab, col_name)),
ListCount = List.Count(LTS),
LstGen = List.Generate(()=>
[Counter=1, RT= LTS{0}],
each [Counter]<=ListCount,
each [Counter=[Counter]+1, RT=([RT]+LTS{[Counter]})],
each [RT]),
in
TblToCol,

// choose column to grouping and to sum
Prod = "Product", // column name for Product
//    Amount = "Sale", // couldn't make it work on the Grouping here
Group = "ProductGroup", // column name for Grouping

Source = Table.Buffer(Excel.CurrentWorkbook(){[Name="SalesProductGroup"]}[Content]),
// you need to specify the name of your column to SUM here directly ([Sale])
TotalsOnProduct = Table.Group(Source, {Prod, Group}, {{"Amount", each List.Sum([Sale]), type number}}),
SortForABC = Table.Sort(TotalsOnProduct,{{Group, Order.Ascending}, {"Amount", Order.Descending}}),
ReorderForABC = Table.ReorderColumns(SortForABC,{"Amount", Group, Prod}),
GroupOnProductGroup = Table.Buffer(Table.Group(ReorderForABC, {Group}, {{"tab", each _, type table}})),
RemoveField = Table.RemoveColumns(GroupOnProductGroup,{Group}),
InvokeFunction = Table.TransformColumns(RemoveField, {"tab", each fxRunningTotalLG(_, "Amount")}),
ShowResults = Table.ExpandTableColumn(InvokeFunction, "tab", {"Amount", Group, Prod, "Running Total"}, {"Amount", Group, Prod, "Running Total"}),
CreateSubtotalOnProductGroup = Table.Group(TotalsOnProduct, {Group}, {{"TotalProductGroup", each List.Sum([Amount]), type number}}),
SelfjoinSubtotals = Table.NestedJoin(ShowResults,{Group},CreateSubtotalOnProductGroup,{Group},"NewColumn"),
ExpandSubtotals = Table.ExpandTableColumn(SelfjoinSubtotals, "NewColumn", {"TotalProductGroup"}, {"TotalProductGroup"}),
Percentage = Table.AddColumn(ExpandSubtotals, "Percentage", each [Running Total]/[TotalProductGroup]),
Classification = Table.AddColumn(Percentage, "Classification", each if [Percentage] < 0.7 then "A" else if [Percentage] < 0.9 then "B" else "C")
in
Classification

Imke

Monday, July 6, 2015 6:11 AM
• Over 3 years later, I found a way to boost performance for this task even further: Basically taking Bills function and instead of applying it to every row of the table (like it will be done in his step :

TransfCol = Table.TransformColumns(RemCol, {"tab", each fxRunningTotalLG(_, CFRT)}),

my new solution will execute the List.Generate-function only once for each table:

https://www.thebiccountant.com/2018/09/30/memory-efficient-clustered-running-total-in-power-bi/

That makes a real difference, so if you after performance here, you should use that new function.

Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com