Running Total based on customer over months

[Guidoow1]

Hi,

I would like to make a running total of my Delta per customer over months. Can't find the right answer anywhere. So hopefully someone can help me.

I have the following data:
Customer
Month
In
Out
Delta

And I would like to create the column: Running total

The running total is the sum of delta over the months per customer.

Example:

Customer A has an Delta of 33 in October. In November a Delta of -56. The running total for november must therefore be -23

[v-stephen-msft]

Hi @Guidoow1 ,

Here's a similar blog which you can refer to

How to Calculate Running Total by Group Using Excel Power Query (exceldemy.com)

Use the function in it

 //Function to Compute Running Totals 

   RunTotalFunction = (RunTotalTable as table) as table =>
   let 
    #"Added Index" = Table.AddIndexColumn(RunTotalTable, "Index", 1, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Running Totals", each List.Sum(List.Range(#"Added Index"[Sales],0,[Index])))
in
    #"Added Custom",

    //Assigning the Function

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Guidoow1]

Hi Stephen,

I tried to incorporate the function in the advanced editor. However, it still doesn't give me te result I like.

The full Editor contains:

let
    Source = SharePoint.Files
    #"File X - DEV xlsx_https://.../"]}[Content],
    #"Imported Excel Workbook" = Excel.Workbook(#"File X/"),
    #"Maandstroom sept_Sheet" = #"Imported Excel Workbook"{[Item="Maandstroom sept",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(#"Maandstroom sept_Sheet", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"klantnr", type text}, {"Klantnaam", type text}, {"Postcode", type text}, {"Maand", type text}, {"Heen", Int64.Type}, {"Retour", Int64.Type}, {"Column7", type any}, {"Column8", type any}, {"Column9", type any}, {"Column10", type any}, {"Column11", type any}, {"Customer", type text}, {"Month", type text}, {"In", Int64.Type}, {"Out", Int64.Type}, {"Delta", Int64.Type}, {"Running Total", Int64.Type}, {"Column18", type any}, {"Column19", type any}, {"Column20", type any}, {"Column21", type any}, {"Column22", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Column7", "Column8", "Column9", "Column10", "Column11", "Customer", "Month", "In", "Out", "Delta", "Running Total", "Column18", "Column19", "Column20", "Column21", "Column22"}),
    #"Added Custom" = Table.AddColumn(#"Removed Columns", "Delta", each [Heen]-[Retour]),
    #"Added Index" = Table.AddIndexColumn(#"Added Custom", "Index", 1, 1, Int64.Type),
    #"Grouped Rows1" = Table.Group(#"Added Index", {"Maand"}, {{"Count", each _, type table [klantnr=nullable text, Klantnaam=nullable text, Postcode=nullable text, Maand=nullable text, Heen=nullable number, Retour=nullable number, Delta=number, Index=number]}}),

    //Function to Compute Running Totals 

   RunTotalFunction = (RunTotalTable as table) as table =>  
   
    let   
    #"Added Index1" = Table.AddIndexColumn(RunTotalTable, "Index", 1, 1, Int64.Type),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Index",{{"Delta", Int64.Type}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type1", "Running Total", each List.Sum(List.Range(#"Added Index"[Delta],0,[Index])))

in
    #"Added Custom1",

    //Assigning the Function
    RunTotals = Table.TransformColumns(#"Grouped Rows1", {"Count", each RunTotalFunction(_)})
in
    RunTotals

Some translation because it is in Dutch:

Month = Maand

In = Heen

Out = Retour

Now it returns to me the running totals of all customers in 1 month. But I need the running total for 1 customers. Where for 1 customer the running total is calculated over the monts month. 

Hope you understand the difference and can help me find te correct answer

[v-stephen-msft · Accepted solution]

Hi @Guidoow1 ,

I made a .pibx file for you. You can download it for checking.

You can expand it.

The whole M language:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XdBNCsIwEIbhu8y6hWQ6DXWpBhcu9AChi7bMshaCCt7e/jmZuCtPP17ChABHKOA+PKee4/xVVdAWAU4Zlg5XPedK29ZnSmbFpXqb3jzyPq6dhDNHk9r6h4prbg6S9zzwuKlFlLpmI+lsTJJWTCTla/d4dfGznKOWbkLrSLpJS4uSVdv03gv38bdt0pkVk5OuHu+n83+M89vaLw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Customer = _t, Month = _t, Delta = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Customer", type text}, {"Month", type text}, {"Delta", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Customer"}, {{"Count", each _, type table [Customer=nullable text, Month=nullable text, Delta=nullable number]}}),

   //Function to Compute Running Totals 

   RunTotalFunction = (RunTotalTable as table) as table =>
   let 
    #"Added Index" = Table.AddIndexColumn(RunTotalTable, "Index", 1, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Running Totals", each List.Sum(List.Range(#"Added Index"[Delta],0,[Index])))
in
    #"Added Custom",

    //Assigning the Function
 RunTotals = Table.TransformColumns(#"Grouped Rows", {"Count", each RunTotalFunction(_)}),
    #"Expanded Count" = Table.ExpandTableColumn(RunTotals, "Count", {"Month", "Delta", "Index", "Running Totals"}, {"Month", "Delta", "Index", "Running Totals"})
in #"Expanded Count"

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.