Forum Discussion
List Generate Average as record;
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 ?
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
12 Replies
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 resultMy oppinion:
First Case (Works as Expected):
avg = asum / xHere, 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 🌀.
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}) / xor
avg = asum / xRegards
Phil
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 ?
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"
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 / xHere 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} ) / xavg 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.
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 ?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.
