Avg of top 3

[#1]

Hi,

I have a pivot table like this:

Test	Best (of 10 runs)	Average (of top 3 out of 10 runs)
1
2

My data is organized in the DB table like this:

Test	Run	Value
1	1	100
1	2	200
..	..	..
1	10	300
2	1	10

I am able to calculate the best of 10 runs, using this expression - if(Match(Test,'1'),Max(Value))

For average - this isnt cutting it - if(Match(Test,'1') and (Rank(Value)<=3),Avg(Value))

Any ideas?

Thanks,

Pavithra

[Miguel_Angel_Baeyens]

Hello Pavithra,

My guess without knowing your data model and expected results would be

Avg(If(Aggr(Rank(Sum(Value), 4, 1), Test) <= 3, Value)

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

[#3]

Sorry Miguel, this didnt work.

I am trying to display the average of the top 3 iterations, for each test in my pivot table column. Not sure why you used Rank(Sum(Value).

Anyway, I tried, your expression and this as well -

Avg(If(Aggr(Rank(Value), 4, 1), Test) <= 3, Value)

and its not working nor is this -

Avg(If (Rank(Value, 4, 1)  <= 3, Value)