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:

Answer 2

Chris,

Try to use the INDEX() functionality rather than the RANK(). For example if you are showing results for the year, your $32,500 value may be ranked 123 of 1000. When filtering to the particular day, that "Rank" value will change to its corresponding rank within that day. Not sure if that is exactly what you were looking for. Here is a tutorial video on the topic. Hope this helps.

Using Index for Rank in Tableau : Video Link

UPDATE:

For Row and Metric Ranking:

• Add the Price Field to the Rows
• Change the price field to an Attribute instead of a Measure
• Make it Discreet
• Click on the Price field in the Rows section, in the dropdown select "Quick Table Calculations" > Rank

For verification of the data, you can add another discrete dimension Price field to the right of the ranking calculation. Everything should work as expected from here.

Additionally, I generated a quick sample in the following link using the Tableau Superstore Sales Data.

Example Project Using Tableau Superstore Sales Data

Eric