How can a calculate a mean of a column, factorizing by other column and considering only distinct values of a third column?

[mlarruda]

Hi. Suppose I have three variables (among many others), as in the example below:

NAME	YEAR	POINTS
John	2020	40
Bob	2020	50
Anna	2021	45
John	2020	40
Bob	2021	47
Louis	2022	42
John	2022	44
Anna	2020	48
Carl	2019	39

My goal is calculate the average points by year. So, for 2021 the result must be (45 (Anna) + 47 (Bob))/2 = 46.

But for 2020 the first and the fourth rows are equal: John - 2020 - 40. So, I want to count each name only once: the desired result for 2020 must be (40 (John) + 50 (Bob) + 48 (Anna))/3 = 46.

How can I get this average as an expression, in a table with YEAR as dimension?

[RsQK · Accepted solution]

Hi, like this?

formula used:

AVG(AGGR(DISTINCT POINTS,YEAR,NAME))

[mlarruda]

It worked! Thank you very much!

Only to confirm my understanding: the DISTINCT function refers to all three columns and, so, I could write AVG(AGGR(DISTINCT NAME, POINTS, YEAR)) or any other order of the columns without change the result, right?

[RsQK]

Yes - distinct values are used. The order does matter - specifically the first parameter is the one you are actually calculating (meaning points should always be first), the order of the rest doesnt matter.  

[mlarruda]

I understood. Thank you very much again.