Combining filtering and Dynamic Ranking in Tableau

Question

My question is as follows: Suppose I have a data set where I have 4 fields

1. Date
2. Type
3. Amount (metric 1)
4. Sell Price (metric 2)

These 4 fields are displayed in a table in my Tableau dashboard. When I click on any item in the table, I would like text at the top to display the rank of both of the metrics and the Unique count of the metrics in my table as well as their values.

e.g. 'Amount = 32,500: Ranked 4 out of 342, Sell Price = $2.5: Ranked 2 out of 6' (I only have 6 differing Sell prices)

I would also like the implementation to be dynamic, so as when I filter on date, the result would adjust to suit e.g. 'Amount = 32,500: Ranked 1 out of 10, Sell Price = $2.5: Ranked 1 out of 4'

I have tried using RANK function, but whenever I click on the a row in my table, it effectively filters out all of the other data and sets the rank to 1.

Is there a way to do this?

Answer 1

I think you won't be able to do exactly what you describe. As you correctly stated, the RANK() function gives you the rank in the current "Partition", that is the data that is actually displayed on your dashboard. If you exclude eg. the row with a rank of 1, the row with the rank 2 will now be 1.

There is no possibility to calculate this based on the rank in the data source (unless you do it outside of Tableau).

If you have the rank in your data source, you can create a dashboard with 4 different sheets (full table, Amount, Rank, Number of Records) and filter the last 3 based on the selection in the first.

As a workaround you could display these values in a tool tip.

• Create a field [NumberofRecords] with the formula Size()
• Drop [NumberofRecords], [Rank] and [Amount] onto the tool tip shelf
• Organise them however you want

As an example it could look like this: