3 Replies Latest reply on Jul 5, 2018 12:21 PM by Ken Flerlage

# Top Records are not filtered Properly

Hey Folks,

Hope everyone busy with work schedule, I have an issue while I used Top records in the worksheet.

Problem:

Filter Top 5 records, and output like below

Expected output: From excel file I have data like below.

Deepak Rai

Shinichiro Murakami

kettan

Joe Oppelt

Attached Sample Files for better understanding.

• ###### 1. Re: Top Records are not filtered Properly

Top X filters are applied to the entire data set--it does not account for the dimensions placed on your view. So, in your case, it's first finding the top 5 projects overall, based on total hours and filtering down to only those 5--d, m, h, k, n. You'll need a different approach if you want to get the top 5 to account for the dimensions on your view. I'd recommend using the RANK functions (Table Calculation Functions). RANK_UNIQUE will give you a unique number (it won't give the same number for ties) so that might be good to use here (but, if you have ties on say the fifth item, it will make one of those 5 and one 6, so if you're only showing top 5, that 6th one will be filtered. If you don't want that then use the RANK_DENSE function). I'd create a calculated field called Rank with the following definition:

Rank

RANK_UNIQUE(SUM([Hours]))

Then create another calculated field as follows:

Top X Filter

IF [Rank]<=[Top 10 Project Hours] THEN

"Include"

ELSE

"Exclude"

END

Add Top X Filters as a filter, then right-click on the pill at edit the table calculation to look like this:

This will account for the dimensions in your view.

See attached workbook. If this solves your problem, please mark this answer as correct. This will help others with similar questions in the future. Thanks!

• ###### 2. Re: Top Records are not filtered Properly

Thank you, Ken.

Great Appreciate for your help. It's working.

• ###### 3. Re: Top Records are not filtered Properly

Great. Happy to hear it!!