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.
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'
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'.
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.