Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
Hi, like this?
formula used:
AVG(AGGR(DISTINCT POINTS,YEAR,NAME))
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?
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.
I understood. Thank you very much again.