New Member

3 years ago

Solved

Running Total based on customer over months

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

Need Help

Anonymous

3 years ago

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.

Running Total based on customer over months.pbix26 KB

3 Replies

Anonymous

Not applicable

3 years ago

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

New Member

3 years ago

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

Anonymous

Not applicable

3 years ago

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.

Running Total based on customer over months.pbix26 KB

Forum Discussion

Running Total based on customer over months

3 Replies

Related content

Running Total Per Month

Running Maximum

Running sum

Total Customers (end of month)

Running Month formatting