Running total issues

[wouter_denckens]

Hi All,

I created a running total in 1 table, but when I link this to another table (unique key, left outer), the running total values get all mixed up, some are correct, but most aren't.   When you look at the actual costs of the year (3rd column), those come in correctly, just the running totals (4th column) go wrong. Is this a common issue? Any suggestions?

Original table:

Linked table:

[wouter_denckens]

Hi Imke,

I realized it had to do with the order of the tables and had a step "sortby" in my code.  I didn't know I had to buffer it.  I was already trying to solve it for 3 days and you just gave me a wonderful solution. THANK YOU!

Best regards

Wouter

[ImkeF · Accepted solution]

Hi @wouter_denckens ,
this could be due to the fact that you are relying on the natural sort order of your input tables.
The index you are adding determines the order of the running total, but there is no sort command for the data in the table itself.
Power Query is using streaming semantics and this could throw things off in subsequent steps like you are experiencing here.
Try inserting a buffered sort step before adding the index. (Bug warning for Table.Sort and removing duplicates... - Microsoft Power BI Community)
Or use this function instead, as it will prompt to enter the name to sort by: Memory efficient clustered running total in Power BI – The BIccountant

[wouter_denckens]

Hi Imke,

I use a function I found on the internet:

= (MyRunningTotalTable) =>

let
#"Index toegevoegd" = Table.AddIndexColumn(MyRunningTotalTable, "Index", 1, 1, Int64.Type),
#"Aangepaste kolom toegevoegd" = Table.AddColumn(#"Index toegevoegd", "RunningTotalPerProject", each List.Sum(List.Range(#"Index toegevoegd"[Amount],0,[Index]))),
#"Kolommen verwijderd" = Table.RemoveColumns(#"Aangepaste kolom toegevoegd",{"Index"}),
#"Type gewijzigd" = Table.TransformColumnTypes(#"Kolommen verwijderd",{{"RunningTotalPerProject", type number}})
in
#"Type gewijzigd"

In this part of my code:

#"Rijen gegroepeerd1" = Table.Group(#"Rijen gesorteerd", {"GLEntryKey"}, {{"MyDataForRunningTotal", each _, type table [GLEntryKey=text, DatumGroup=nullable date, Amount=nullable number, DateNextPeriod=nullable date, LinkActPeriod=text, LinkPreviousPeriod=text, SortColumn=text]}}),
#"Aangeroepen aangepaste functie" = Table.AddColumn(#"Rijen gegroepeerd1", "MyData2", each fnRunGroup([MyDataForRunningTotal])),
#"Kolommen verwijderd1" = Table.RemoveColumns(#"Aangeroepen aangepaste functie",{"MyDataForRunningTotal"}),
#"MyData2 uitgevouwen" = Table.ExpandTableColumn(#"Kolommen verwijderd1", "MyData2", {"DatumGroup", "Amount", "DateNextPeriod", "LinkActPeriod", "LinkPreviousPeriod", "SortColumn", "RunningTotalPerProject"}, {"DatumGroup", "Amount", "DateNextPeriod", "LinkActPeriod", "LinkPreviousPeriod", "SortColumn", "RunningTotalPerProject"}),

Thanks in advance!!

Best regards

Wouter

[ImkeF]

Hi @wouter_denckens ,
how do you calculate the running total? Please show the M-code you are using.