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?

Shown

When you say "are held in a table" do you mean that's the structure of your data source or that you created a table in a dashboard that looks like that? – Airlee

Thanks for the reply Alex. I am referring to a table in my dashboard. – Shown

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:

Airlee

Thanks for the detailed response Alex. It's unfortunate that Tableau doesn't allow for this. Using DAX, the simple ALL() function would do the trick. At the moment, I have hardcoded the values (like you suggested), but it doesn't really offer the end user the flexibility I was after. While your other suggestion of a tooltip display would also work, I was hoping to display the rank in a large font at the top of my dashboard. The table on my dash wold only take up half the page. The other half would be dedicated to four KPIs along with their appropriate rank to give them context. – Shown

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

Uncircumcised

Hey Eric, I'm sorry but I simplified my actual question too much. While the answer you provided is correct, I was wanting to do this for multiple columns. I have updated the question to better explain my problem. – Shown

Chris, sorry for the delay... hell of a week. Please see my revised response that should answer your additional questions. – Uncircumcised

Hey Eric, everything you sent through makes sense. Thanks so much. The final piece of the puzzle is passing this rank up to the top up my dash where I can see it outside of the table. Currently exploring URLs to do this in combination with web containers. Fingers crossed... – Shown

May want to look at doing a calculated field that will concatenate a text string containing your dynamic rankings. This way you can just reference the field from your dashboard. Good Luck! – Uncircumcised

Totally - but how do you do this? I haven't been able to make this work in Tableau. Been butting my head against the wall for ages. – Shown

I mean passing a calculated filed into a text string. It should seem easy enough, but it only looks like I can pass parameters into strings... – Shown

Hello Chris, In the example files that I had provided, create a calculated column. In the formula box, insert the following: (posted in a separate comment so it is easier to discern). This is converting your initial "Item rank" from a number to a string and allowing the table Rank function to operate the same way by using the average (default group by). I tested in the sample project and this works. – Uncircumcised

"This Product has an Item Rank of " + STR([Rank]) + ", and a Unit Price Rank of " + STR(RANK(AVG([Unit Price]))) – Uncircumcised

Hi Eric, I've attached a screenshot here of what I am trying to achieve spaces.hightail.com/space/467Hm. Do you know if this is possible? While the I can create the text in a calculated column (like you suggested), what I am unable to do is get it to show up in a text container at the top of my dashboard. – Shown

The formula in my column is: 'Ranked ' + STR(RANK(AVG([Unit Price]))) + ' out of ' + str(WINDOW_MAX([Rank])). Just in case you have trouble opening the .tbx due to version issues. – Shown

Chris, I do not think there is a way to do this completely within Tableau, since Tableau's ToolTip functionality already does this for a selection... The only way I see this working is to generate a simple web page that will accept the text in the query string. You can then add a dashboard action of "URL" that sends the selected tooltip to the new page which displays text passed through query string. You can then add a web page object to your dashboard. However, IMHO, it seems a bit excessive for a work around when the data is a cursor hover away. – Uncircumcised

Hey Eric, this is the same conclusion I have arrived at too and am currently in discussions with our tech team. I agree that it's a bit excessive, but if the solution works, we will be able to roll it out to multiple clients. I can really not thank you enough for all of your help with my problem - it's people like you that make Stack one of my favorite sites! All the best, Chris – Shown