Excel Forum

Using a resetting total to manage groupings

William Holloway

Active Member

Members

Forum Posts: 3

Member Since:
July 10, 2021

Offline

July 10, 2021 - 3:47 am

I am trying to create a column that will reset the running total to 0 or the next value in the list when the running total value hits a specific number (10000 in this case)

So it is taking in a list of numbers, adding them I need to figure out why the validation of the current sum value isnt working.

fxRunningGrouping

(values as list) as list =>
let
RT = List.Generate
(
()=> [RT = values{0}, counter = 0],
each [counter] < List.Count(values),
each if {[RT]} >= 10000 then [RT = values{[counter]}, counter = [counter]+1] else
//each if _>= 10000 then [RT = values{[counter]}, counter = [counter]+1] else
each [RT = [RT]+values{[counter]+1}, counter = [counter]+1],

each [RT]
)
in
RT

See Answer

Catalin Bombea

Iasi, Romania

Member

Dashboards

Power Query

Power Pivot

Xtreme Pivot Tables

Excel for Decision Making

Excel for Finance

Excel Analysis Toolpak

Power BI

Excel

Word

Outlook

Excel Expert

Excel Customer Service

PowerPoint

Forum Posts: 1944

Member Since:
November 8, 2013

Offline

July 11, 2021 - 3:33 am

Hi William,

Looking at:

each if {[RT]} >= 10000

The curly brackets indicate a list type, normally, you cannot compare a list to a number, you have to make sure you compare the same data types.

William Holloway

Active Member

Members

Forum Posts: 3

Member Since:
July 10, 2021

Offline

July 13, 2021 - 4:13 am

So I added a column that just has 10000 as the value would I be able to do something like

if [RT] >= [newColumn] then [RT = values{[counter]}, counter = [counter]+1] else ....

The idea is to reset the current total to the current value in the list. Thus resetting the total.

What happens if it doesnt reset is the addition doesnt come out right because the values can be anything.

Catalin Bombea

Iasi, Romania

Member

Forum Posts: 1944

Member Since:
November 8, 2013

Offline

July 13, 2021 - 1:38 pm

Hi William,

Here is an example based on:

https://www.myonlinetraininghu.....ower-query

>code>

(values as list) as list=>
let
RT = List.Generate
(
()=> [RT = values{0}, counter = 0],
each [counter] < List.Count(values),
each [RT = if [RT]>=10000 then 0 else [RT] + values{[counter] + 1}, counter = [counter] + 1],
each [RT]
)
in RT

If you don't want to reset to 0 but to the current row value (which makes more sense):

each [RT = if [RT]>=10000 then values{[counter] + 1} else [RT] + values{[counter] + 1}, counter = [counter] + 1],