Jagran: Assuming you have used a 'Top_20' function to Rank and flag the Top_20 records in the subset, the formula would be a Subset_Mean function of the form:

Subset_Mean([Field to be Averaged]),
Subset2([FieldwithRanking],[AnotherField],
'Top_20',[AnotherField],
=,=)

Note that in the Table View, the results of Formulae are global and do not respond to filtering, as they do inside views like the Content View.

Here is a link to the Top/Bottom 'n' discussions:

http://forums.visokio.com/discussions/tagged?Tag=top%2Fbottom_n_records

Thank you tjbate for replying back. Please see the attached file.

I have used the formula
IF(RECORDCOUNT()<=50, SUBSET_MEAN([Score],SUBSET([Category],"ORACLE")), 0)<br />
My requirement is:
1)If Filtered data has record count less then or equal to 50 then I want average score of all filtered records having category ="ORACLE".

Problem is with else part where I have put 0 as of now.The requirement is:

2)IF filtered data has records more then 50, then calclulate the average of top
20% scores having category='ORACLE'.

i.e. Suppose filtered data has 100 records then average of lowest 20 score having category='ORACLE'

Please help.

It is best to calculate whether a record's ranking is 'Top-20' or not/null in a separate formula field. If you do this in a Formula field using the RANK function named something like [RankingFormulaField], the rest of the expression is just another Subset function that merely looks at whether a record is both 'Top-20' AND 'ORACLE'.

SUBSET-MEAN([Score]), SUBSET2([RankingFormulaField],[Category],
'Top-20','ORACLE',
=,=)

Note that "top 20" records and "top 20%" of records (by what weighting?) are different concepts, and automatic Pareto rankings like Top 20% by defined weight are being discussed in the Ideas sections under the tag Pareto.

Please attach one example.

Jagran - The attached is a general solution for defining top/bottom N records, and calculating sub-rankings and sub-averages for top/bottom n subsets.