Forum Discussion
AdministratorHelp with a reset running total on two criteria
Hello,
New poster so go gentle with me!
My issue is fairly straight forward but I just don't seem to be able to solve it.
I have 3 columns of data:-
POSplitRows - This is a grouping of data lines based on other criteria not relevant to this issue.
Units - Values attributable to each "POSplitRows" line
Running Total - Running total of "Units" that I would like to reset when two criteria are reached 1. Reset when the "POSplitRows" grouping changes and 2. Reset within each "POSplitRows" grouping if the running total exceeds or equals 5000.
My solution
1. Extracted the columns needed from source data
2. Buffered "Units" & "POSplitRows" as lists
3. Created a custom function with the code below:-
= (values as list) as list =>
let
GRTList = List.Generate(
()=> [ GRT = values{0}, i = 0 ],
each [i] < List.Count(values),
each try
if [GRT] + values{[i]} <= 5000
then [GRT = [GRT] + values{[i] + 1}, i = [i] + 1]
else [GRT = values{[i] + 1}, i = [i] + 1]
otherwise [i = [i] + 1] ,
each [GRT] )
in
GRTList
4. Applied this function code to the data at stage 2 using the custom code below:-
= Table.FromColumns(
{
Source[POSplitRows], Source[Units],
fxRunningUnitGroupx(BufferedUnits, BufferedIndex)
},
{
"POSplitRows",
"Units",
"Running Total"
})
Problem
The split to reset at 5000 units seems to be working as expected (Yellow box below) but the reset based on the "POSplitRows" grouping is performed on the second line of the new "POSplitRows" grouping (Red Box below). I want the "Running Total" column to show the first line of the new grouping as the new running total (ie Start at 404 units, not 287)
I think it is something not quite right in my fuction coding above. If anyone can offer any ideas I would be grateful.
Thanks.
7 Replies
- amustafa
Solution Sage
Easier to do this in a calulated column using DAX vs. Power Query. Adjust the table name accordingly.
Running Total =VAR CurrentPOSplitRows = 'Sample'[POSplitRows]VAR CurrentIndex = 'Sample'[Index]VAR RunningTotalSoFar =CALCULATE(SUM(Sample[Units]),FILTER('Sample',Sample[POSplitRows] = CurrentPOSplitRows &&Sample[Index] <= CurrentIndex))VAR ResetRunningTotal =RunningTotalSoFar -CALCULATE(SUM(Sample[Units]),FILTER(Sample,Sample[POSplitRows] = CurrentPOSplitRows &&Sample[Index] < CurrentIndex &&RunningTotalSoFar >= 5000))RETURNIF(ResetRunningTotal >= 5000,Sample[Units],ResetRunningTotal) 
Syndicate_AdminMany thnaks for this.
I forgot to mention I am using the Power Query engine behind Excel and not Power BI.
Can I enter the DAX code or can it be adapted for Power Query for Excel?
Thanks again
- amustafa
I was afraid you gonna say that:-)
In power query its a matter of creating a function. I'll see what I can do. Stay tuned
- Syndicate_Admin
Many thanks.
I attempted some code in a function in my original post. It really is just offsetting the POSplitRows by one line as described above. It may just be something I have missed in that code above.
- amustafa
Got it to work 🙂
let
Source = Excel.Workbook(File.Contents("C:\Users\aliom\OneDrive - MS365\Power BI Samples\Running Totals with reset\Sample.xlsx"), null, true),
Table1_Table = Source{[Item="Table1",Kind="Table"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(Table1_Table,{{"POSplitRows", type text}, {"Units", Int64.Type}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type),// Function to calculate running total with reset logic
AddRunningTotal = (table as table) as table =>
let
RowCount = Table.RowCount(table),
RunningTotalList = List.Accumulate(List.FirstN(table[Units], RowCount), {},
(state, current) =>
let
LastTotal = if List.Count(state) = 0 then 0 else List.Last(state),
LastRow = if List.Count(state) = 0 then null else table{List.Count(state)-1},
LastPOSplitRows = if LastRow = null then "" else LastRow[POSplitRows],
NewTotal = if LastTotal >= 5000 or (LastRow <> null and LastRow[POSplitRows] <> table{List.Count(state)}[POSplitRows]) then current else LastTotal + current
in
state & {NewTotal}
),
RunningTotalColumn = Table.FromColumns({table[Index], table[POSplitRows], table[Units], RunningTotalList}, {"Index", "POSplitRows", "Units", "Custom"})
in
RunningTotalColumn,// Apply function to add Running Total column
FinalTable = AddRunningTotal(#"Added Index")
in
FinalTable- Syndicate_Admin
Hi, many thanks for this.
My source is a query rather than an excel file. I have remapped the source to the Query within Power Query.
It is taking a long time to run. Do I need to remap any other elements of the code to the discrete query name of my source within the function part of the coding?
- amustafa
Regardless of the source, you can add an index column to your data and then copy from my code after creating the index step...
//Your existng code
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type),
// Function to calculate running total with reset logic
AddRunningTotal = (table as table) as table => ....
