6

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?

Chris
  • 629
  • 2
  • 13
  • 31
  • 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? – Alexander Apr 19 '16 at 03:03
  • Thanks for the reply Alex. I am referring to a table in my dashboard. – Chris Apr 19 '16 at 13:38

2 Answers2

5

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: enter image description here

Alexander
  • 1,889
  • 15
  • 29
  • 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. – Chris Apr 20 '16 at 14:38
4

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.

Dashboard With All Data

Dashboard With Data Filtered on Single Date

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

Example Project Using Tableau Superstore Sales Data

Eric

Eric D
  • 486
  • 3
  • 10
  • 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. – Chris Apr 27 '16 at 21:00
  • Chris, sorry for the delay... hell of a week. Please see my revised response that should answer your additional questions. – Eric D May 02 '16 at 16:44
  • 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... – Chris May 04 '16 at 19:53
  • 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! – Eric D May 05 '16 at 13:20
  • 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. – Chris May 06 '16 at 15:59
  • 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... – Chris May 06 '16 at 16:22
  • 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. – Eric D May 09 '16 at 21:16
  • "This Product has an Item Rank of " + STR([Rank]) + ", and a Unit Price Rank of " + STR(RANK(AVG([Unit Price]))) – Eric D May 09 '16 at 21:19
  • Hi Eric, I've attached a screenshot here of what I am trying to achieve https://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. – Chris May 16 '16 at 14:18
  • 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. – Chris May 16 '16 at 14:21
  • 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. – Eric D May 18 '16 at 16:59
  • 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 – Chris May 19 '16 at 18:35