5 Replies Latest reply on Jan 25, 2017 11:14 AM by Nasir Khan

# Show rank without showing all rows when filtering

I know there a few articles on ranking out there but I was unable to get the exact answer for my situation. So I apologize in advance for this topic being repeated.

I want a way to show a customer’s ranking for a particular year or the sum of all years however I want a quick filter on the customer. I want to be able to filter to any customer and show what their ranking was for every year and the sum of all years.

My data set looks something like this:

 Customer 2015 Sales 2015 Rank 2016 Sales 2016 Rank 2017 Sales 2017 Rank Total Total Rank Alex \$101 10 \$372 8 \$335 8 \$808 10 Bill \$713 4 \$257 9 \$592 5 \$1,562 6 Bob \$221 8 \$388 7 \$708 3 \$1,317 7 Henry \$869 2 \$457 6 \$694 4 \$2,020 2 Jennifer \$723 3 \$185 10 \$230 9 \$1,138 9 Kendra \$242 7 \$871 3 \$845 1 \$1,958 3 Mike \$618 5 \$486 5 \$122 10 \$1,226 8 Sarah \$188 9 \$943 1 \$732 2 \$1,863 4 Stacy \$950 1 \$909 2 \$565 6 \$2,424 1 Tom \$364 6 \$812 4 \$438 7 \$1,614 5

And I want to be able to get this if I filter to Kendra on customer:

 Customer 2015 Sales 2015 Rank 2016 Sales 2016 Rank 2017 Sales 2017 Rank Total Total Rank Kendra \$242 7 \$871 3 \$845 1 \$1,958 3

• ###### 1. Re: Show rank without showing all rows when filtering

Your problem is that the RANK function is a table calculation, and so gets calculated after regular filters. Try instead filtering on the following calculated field

[Customer Filter] : LOOKUP(ATTR([Customer]),0)

I would recommend that you have a look at the following article for more information about Tableau's Order of Operations:

1 of 1 people found this helpful
• ###### 2. Re: Show rank without showing all rows when filtering

Nasir,

As Stephan says, lookup filter works, but problem is on G.Total with table calc.

to show G.Total with year break down, we need duplicated data.

I used "Union" here.

if [Table Name]="Sheet4" then str(year([Year]))

elseif [Table Name]="Sheet41" then "Total" end

[Rank]

RANK_UNIQUE(sum([Sales]))

[Customer Filter]

lookup(min([Customer1]),0)

Thanks,

Shin

• ###### 3. Re: Show rank without showing all rows when filtering

Sorry, at version 9.3, G.total worked.

Thanks,

Shin

1 of 1 people found this helpful
• ###### 4. Re: Show rank without showing all rows when filtering

Thanks Stephen! I cannot believe how simple this was.

• ###### 5. Re: Show rank without showing all rows when filtering

Thanks for your effort! But yes, in 9.3 it can be done.