Average of N largest values

[aus94]

Hi everybody,

I am working on a line plot, where I want to plot the average of the N largest values of a column over the X axis. I manage to average across the 3 largest values with the following expression, however I want to make it more generic and use a variable to control the top N values on which I want to average. Is it possible?

Thanks!

(NthLargest([var],1) + NthLargest([var],2) + NthLargest([var],3)) / 3 as [var [Avg 3 largest]]]

[Gaia Paolini]

I can think of doing it with 2 calculated columns. 

First define a document property, Nvalue, containing your desired N.

Then  define a column containing the sum of the N largest values, call it e.g. [sum_N_largest]:

case when [var]>= NthLargest(DISTINCT([var]),${Nvalue}) then Sum([var]) end

This will give you the sum over all values in the top N, but it will be empty for the rows where the value is not in the N largest.
So the second column creates the max value of that (since a defined value is always greater than None) and divides it by the N:
This would be your result column, if creating a calculated column, or expression for your plot.

Max([sum_N_largest])/${Nvalue}

This column will be defined for every row.
I notice that NthLargest only returns the true Nth value if all the records are distinct.  So I amended the call to NthLargest adding Distinct.
I am not 100% sure of what you want to calculate though. You can keep or remove the Distinct(..) as needed.

Edited June 20 by Gaia Paolini