How to sort grouped items based on average of values

[Maurizio_Ortenz]

I know i can group items by one column and sort by another existing columns, but I need to order based on a value calcolated from multiple rows and not just by one (eg. Average of values).
I have a list of of record with names and associated points for each match, then I group by name and “Group aggregate” by average of points of each person. In this this way i have a table with all the names of players with their associated average points.
The problem is that I want to order the list based on the calculated average points.
Example:
Tom 100 points
Jerry 50 points
Tom 200 Points
Jerry 100 points

I would like the UX view to show Tom with its average of 150 points before Jerry with its average of 75 points.

[Steve]

A view can only sort by column values, therefore you must add a column with the desired sort value.

[Maurizio_Ortenz]

Thank you and in this case i would need a formula to write in each row the average of points for each name in the [name] column. Is there such a formula?
In my example i would have to add to all rows of TOM the number (100+200)/2 and in the rows of Jerry it’s average (75).
Is there such a Function or formula?
we should have a formula that select all rows with TOM and does the average of it’s points.

[Steve · Accepted solution]

To get all of this player’s stats:

SELECT(table[stat-column], ([_THISROW].[Name] = [Name]), FALSE)

replacing table with the name of the table containing the stats, and stat-column with the name of the column containing the stat you want to average.

See also:

help.appsheet.com

AVERAGE()

Arithmetic average of list items

help.appsheet.com

COUNT()

Number of list items

help.appsheet.com

SELECT()

Gather column values from row in table or slice

[Maurizio_Ortenz]

succesfully created a virtual columns [Punteggio Medio] with formula:average(SELECT(row2[Punteggio Globale], ([_THISROW].[Name] = [Name]), FALSE))[Punteggio Globale].
Now if i sort rows by [Punteggio medio] and group by name ascending i see names ordered alphabetically and not as I expected ordered by Average points calculated with new formula . Is there a solution to group by name and order names by thet average points?
The closest I can get is to sort by average column and to group by average column, but in this way I get the name of player repeated many times.

if order by the average column but group by name, the results is in the format I like but the names are ordered alfabetically like in the following picture:

[OptimiX_XcrY]

I would have a table with names (maybe you already do) , and in that table I would create VC for the average points, and I would display that table instead. The original table cand be reffed to this new table.

It can probably speed up performance too, because VC will be calculated only once for each name by checking only once the original table for every name. VC for your original table will be run for every row