List Generate Average as record;

[Dicken]

Hi, can someone explain what is happening here;   I have a standard accumulaton, with two outcomes 
one for the sum and one for average ; 

 let alist = List.Numbers( 1, 20 ,1 ) 
in 
List.Generate( ()=>   [ x = 1  , asum = alist {0} ,avg = alist {0} ] , 
each [x] < List.Count( alist) , 
each [ x = [x] + 1, asum = [asum] + alist {x-1}  , avg = ( [avg ] + alist {x -1} ) ] )

so great both asum and avg  = same result but is i then use ;;

= let alist = List.Numbers( 1, 20 ,1 ) 
in 
List.Generate( ()=>   [ x = 1 , asum = alist {0} ,avg = alist {0} ] , 
each [x] < List.Count( alist) , each [ x = [x] + 1, asum = [asum] + alist {x-1}   , avg =  asum / x ] 
)

so avg =  asum / x 

this works returning the average for each step  but if i use; 

= let alist = List.Numbers( 1, 20 ,1 ) 
in 
List.Generate( ()=>   [ x = 1  , asum = alist {0} ,avg = alist {0} ] , 
each [x] < List.Count( alist) , 
each [ x = [x] + 1, asum = [asum] + alist {x-1}  , avg = ( [avg ] + alist {x -1} ) / x  ] )

so   avg = ( [avg] + alist {x-1} ) / x   

this does not give the correct result even thoug in the first example  [asum] + alist {x-1} = [avg] + alist {x+1}   
so what's going on ?  

[Shubham_rai955]

The key point: [avg] is already an average, not a sum. So adding the next value to it and dividing by x again is mathematically wrong.​

What each version does

• Working version

  avg = asum / x

  Here asum is the running sum of the first x numbers, so asum / x is the correct running average.​

• Non‑working version

  avg = ( [avg] + alist{x-1} ) / x

  avg at step x-1 is already (sum of first x-1 values) / (x-1). When you do avg + alist{x-1}, you are adding a mean to a raw value, not to the sum, so the result is no longer proportional to the true sum. Dividing this by x cannot fix it, so the running average drifts away from the correct value.​

To keep a running average with List.Generate, always maintain a running sum (like asum) and compute avg = asum / x or recompute avg from that sum each step.​

[Dicken]

so let me see if i'm coming clsose becasue if i take 

= let alist = {1..10} in
List.Generate( ()=> [ x = 1 , y = alist {0} , z = alist {0} ] ,
each [x] <= List.Count( alist ) ,
each [ x = [x] + 1, y = [y] + alist {x-1} ,
z = ( [z] + alist {x-1} ) ] )

both x and y  = 6 at the third step, but if id divide  ( [z] + alist {x-1} )  / 2 = 2.25, , 
are you saying it is because it is part of an 'on going' accumulation and not an actual salcer value 
than can be divded ? 

[jgeddes]

You are correct. It is an ongoing accumulation. 
So when you write avg = ([avg] + alist{x-1})/x you would be storing the value you are calculating with that expression into the avg variable. That variable value is then used in the calculation in the next iteration in the list. 

[PhilipTreacy]

Hi @Dicken 

This is not the correct way to calculate the average

avg = ( [avg ] + alist {x -1} ) / x

[avg] already contains the average of the previous x-1 values, not the sum of those values. 

You can't just add the next value to [avg] then divide by x 

The correct average calculation is either

avg = ([asum] + alist{x-1}) / x

or

avg =  asum / x

Regards

Phil

[Dicken]

i know it does not work the quesiton was why; 
if ;

each [x] < List.Count( alist) , 
each [ x = [x] + 1,  asum = [asum] + alist {x-1} , 
avg = [avg] + alist {x-1} ] )

here  asum and avg = same result and x = same result why 

avg = (  [avg] + alist {x-1} ) / x , 
avg2 = asum /x ] )

does avg not work and avg2 work, as  before divtins asum = avg ?

[PhilipTreacy]

@Dicken 

I did explain why it doesn't work 🙂

Let's say you have a list of 3 numbers {2 , 3, 4}

Start with x = 1

Sum = 2

Correct Avg = 2/1 = 2

Your avg = 2/1 = 2

x = 2

Sum = 2 + 3 = 5

Correct Avg = (2 + 3) / 2 = 2.5

Your avg = ([Prev Avg] 2 + 3 ) / 2 = 2.5

All good so far.

x = 3

Sum = 5 + 4 = 9

Correct Avg = (2 + 3 + 4) / 3 = 3

Your avg = ([Prev Avg] 2 .5 + 3 ) / 3 = 1.83333

The way your code was calculating avg is taking the avg from the previous step and adding the next value then dividing by x, which is incorrect

Regards

Phil

[Dicken]

ok  but you state ;   Your avg = ([Prev Avg] 2 .5 + 3 ) / 3 = 1.83333   , 

but if you don't do the divsion   it's the correct result example 

[PhilipTreacy]

@Dicken 

I'm not sure what you mean when you say if you don't do the division it's the correct result ?

If you don't divide the sum by anything how do you get an average?  And how can it be correct? 

For the way you are calculating the average if you don't do any division you get a sum of 2.5 + 3 = 5.5.  Then what?

The correct sum when x = 3 is 2 + 3 + 4 = 9, not 5.5.

Sorry I'm not sure how else to explain this.  If you follow my example I'd have thought that woud make it clear.  Maybe try doing my example on paper as working through something by hand can often help understanding rather than reading code and math.

Phil

[Dicken]

ok   but is still dos not seem right ; if i have 
= let alist = {1..10} in
List.Generate( ()=> [ x = 1, y = alist {0} ,
z = alist {0} ] ,
each [x] < List.Count( alist) ,
each [x = [x] + 1, y = [y] + alist {x-1} ,
z = [z] + alist {x-1} ] )  
so here   y = z   but    z / 2   <>    z = y/2   i did pop it into copilot and this si the link 
but you seem to get it more than i do. not sure if copiliot links allowed? if not sorry. 
https://copilot.microsoft.com/shares/KN8qNunQvmG3Xr88jWNfw

[PhilipTreacy · Accepted solution]

@Dicken 

I'm not sure what you are asking here.

Clearly in the code z and y are always the same.

But what is this?  It makes no sense.  Where are you getting this from?

 z / 2   <>    z = y/2

You can't say a thing (z/2) is not equal (or equal) to another thing (z=y/2). 

You can't have an assignment on one side of a logical test like this

Again not sure how esle to explain this other than how I have.

 z / 2   <>    z = y/2 makes no sense mathematically and I don't know what you are trying to do there.

Phil

[jgeddes]

Maybe this table helps illustrate what is happening here. 

Avg1 is [avg] + alist{x-1}

Avg2 is ([avg] + alist{x-1)/x)

let
    Query2 = let alist = List.Numbers( 1, 20 ,1 ) 
in 
List.Generate( 
    ()=>   [ x = 1  , asum = alist {0} ,avg1 = alist {0}, avg2 = alist {0}] , 
    each [x] < List.Count( alist) , 
    each [ 
            x = [x] + 1, 
            previousAvg1 = [avg1],
            previousAvg2 = [avg2],
            asum = [asum] + alist {x-1}  , 
            avg1 = ( [avg1] + alist {x -1} ),
            avg2 = ( ([avg2] + alist{x-1})/x )
        ]
),
    #"Converted to Table" = Table.FromList(Query2, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"x", "previousAvg1", "previousAvg2", "asum", "avg1", "avg2"}, {"x", "previousAvg1", "previousAvg2", "asum", "avg1", "avg2"})
in
    #"Expanded Column1"

[Zanqueta]

Hi @Dicken,

Let me know if thats it are you expecting for:

Script tested: 

let
    alist = List.Numbers(1, 20, 1),
    result = List.Generate(
        () => [x = 1, asum = alist{0}, avg = alist{0}],
        each [x] < List.Count(alist),
        each [
            x = [x] + 1,
            asum = [asum] + alist{[x]-1},
            avg = [asum] / [x]
        ]
    )
in
    result

My oppinion:

First Case (Works as Expected):

avg = asum / x

Here, avg depends on the current value of asum and x, which have already been updated in the same iteration. So at each step:

• asum = cumulative sum up to current x
• avg = cumulative sum divided by current count
  This gives the correct running average.

Second Case (Incorrect Result):

avg = ([avg] + alist)

Here, you are using the previous value of avg and adding the new element, then dividing by x. This introduces a logical error:

• avg is not the sum; it is already an average from the previous step.
• Adding alist{x-1} to an average does not produce a correct cumulative sum.
• Then dividing by x again compounds the error.

Essentially, you are mixing average logic with incremental sum logic, which breaks the calculation.

Why does the second formula fail?
Because avg is not a sum, so adding a new element to it does not represent the cumulative total. The correct formula must always reference the cumulative sum (asum), not the previous average.

If this response was helpful in any way, I’d gladly accept a 👍much like the joy of seeing a DAX measure work first time without needing another FILTER.

Please mark it as the correct solution. It helps other community members find their way faster (and saves them from another endless loop 🌀.