VizWiz

Launch, grow, and unlock your career in data

November 29, 2014

Tableau Tip: Conditional Axis Formatting Using an Axis Selector

3 comments
Back in July 2012, I wrote about "Dynamic Axis Selections". The problem with this approach, though, was that it created a single axis, which only allows for a single format.  Reader Dave Andrade posted a different, yet related question in the comments.
Let's say we choose 3 metrics - Mail Volume, Spend, and Response Rate as part of our parameter metrics. That means we now have a regular number, a dollar value, and a percentage as part of our available measures. After building the case statement and building a chart similar to the one you've shown in this post, the best thing we can do to show the correct values (sans label of $ or %) on the y-axis is using the "automatic" number formatting option for the 'Measure chosen' measure. I know Tableau has made it much easier to add the label on the chart itself with the Label mark after v8.0, but what about the actual y-axis value? Is there any way to dynamically update the measure value's axis label to reflect it's true number format? 
While the direct answer to Dave's question is no, there's isn't a way to dynamically update the format of the Measure Values pill, there is a work around using containers, which will can give the perception of conditional formatting. Here is the final output of the technique I've used. Read farther down for detailed step-by-step instructions, plus a video.

Download the workbook here.

In this example, I've created a simple dashboard with three viz types: Line Chart, Map, and Bar Chart. I created three separate sheets for each type of chart, one for each metric: Sales, Profit Ratio, and Order Quantity, each having a different format for a total of nine worksheets.

Step 1 - Create the lines charts. I started with Sales and then duplicated the sheet and replaced Sales with Profit Ratio and Order Quantity, leaving me with three separate worksheets.


Step 2 - Create a map for each metric. Again, I end up with one worksheet for each metric.


Step 3 - Create a bar chart for each metric, giving us three more worksheets for a total of nine.


Step 4 - Create a parameter with a list of the metrics.


Step 5 - Create a calculated field to get the value selected in the parameter created in Step 4.


Step 6 - Show the parameter control on one of the Sales worksheets and choose Sales from the list.


Step 7 - For each of the worksheets that is using the Sales measure, drag the calculated field created in step 5 onto the Filters shelf and explicitly choose Sales from the list. There should only be one item in the list of available selections. Do NOT choose the "Use All" option or this hack won't work.


Note what happens when you change the selection in the parameter (e.g., choose Profit Ratio); the worksheet will go blank. This is exactly what we want because we want the Sales worksheets to go blank when a different metric is chosen.  Go ahead, give it a try.

Step 8 - Change the metric chosen in the parameter to Profit Ratio and then add the calculated field created in step 5 onto the Filters shelf for each of the worksheets that contains Profit Ratio and explicitly choose Profit Ratio from the list.


Again, notice how Profit Ratio is our only available selection in the filter list.

Step 9 - Repeat step 8, but this time choose Order Quantity and add the filter to each of the Order Quantity sheets.

Step 10 - Create a new dashboard.

Step 11 (optional) - Add the title to the dashboard and in the title, include the parameter. This way the title will make the dashboard more intuitive.


Step 12 - Add two horizontal containers to the dashboard, one above the other.

Step 13 - Add the three line charts to the top container...


...then hide the titles for each worksheet.


Step 14 - Add all of the maps and bar charts to the bottom container...


...then hide the titles for each worksheet.


Step 15 - A bit of cleanup, like sizing the charts to fit the view, removing the color legends, making it all looks pretty and floating the parameter and you're all done!


Go back to the viz at the top to play with the interactive version. Here's a video recording of how I built this dashboard.

3 comments :

  1. sorry for my english. One problem: the tooltip in the graph does not appear highlighted

    ReplyDelete
    Replies
    1. I'm not sure I understand your question. The tooltips are working as designed and as expected. I believe I'm misunderstanding what you are asking though. Can you please try to explain so that I can help?

      Delete
  2. I am encountering a samll problem here.
    I am working in 10.3 and there a thing that they have changed. The last step you did there where you hide the title and the sheet collapses to a single line, doesn't happen anymore. So, now what I did instead, is make the other sheets as floating and put it one over other, which works for the view and changes as expected on selecting different parameter. But, you can't select the lower sheets anymore, due to the presence of empty floating sheets over it, hence it strips down the tooltip functionality too. Please suggest a way to overcome this. Thank you!

    ReplyDelete