Forum Discussion
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...
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.
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.
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
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.