How to make an average excluding the 2% biggest values ?

[fredericvillemi]

Hello, I have a list of incidents with a duration and i would like to make an average but excluding the 2% biggest values

for example i have :

duration / in scope

100 1

50 1

1000 1

3 0

3000 0

and i make a sum(duration) / sum(scope)

but i want to exclude the 2% biggest values of duration. How can I do ?   If i remove the 3000 I also need to take into account that it was 0 in scope .. I presume i can do it with a aggr but i can't find

Thanks in advance 

[sergio0592 · Accepted solution]

Hi,

For exclude the biggest value in the average calculation, you can use (i suppose you have a key field) :

=sum({<Key-={"=sum(Duration)=max(TOTAL Duration)"} >} Duration) / sum({<Key-={"=sum(Duration)=max(TOTAL Duration)"} >} Scope)

[fredericvillemi]

I think my case is more complicated. Your example works in the example i had given but i can't make it work in a more complicated version.

I have a list of incidents (with unique ids) and i want to make the average duration of incidents only if they don't belong to the 2% longest of the Month.

I have managed to do something by precalculating the fractile(0.98) of the month but i don't see how to do without precalculation ..