Launch, grow, and unlock your career in data

October 11, 2017

Workout Wednesday: State to City Drill Down

This week's challenge started with a Convo challenge at The Information Lab from Chris Love. In Chris' challenge, he created a scatterplot that allows the user to click on a State and drill down to the City level. That is the starting point for this week's challenge and I've taken it to another level.

As a hint, in Chris' version, all cities were in the initial view, but for each State, all of the cities in that State would display the same value until you click on the State. The drawback of this method is that displaying mark labels (i.e., State names) only looks decent when a State only has sales in a single city. I want to show the labels for all States that have enough space to display it. In other words, the initial view should show only one dot per State, then when you drill down, you will see one dot per City in the selected State.

Before you start, I would highly encourage you to interact with the viz below to see how the drill down works.

Using this data source, here are the requirements:

  1. Create a scatterplot of sales vs. profit.
  2. In the initial view, you should see only one dot for each State. If you do this part right, you will have 48 marks in the view.
  3. Label the dots for the States in the initial view where the labels fit (using Tableau's automatic labelling).
  4. If you click on one of the States, then the view should automagically drill down to the cities for the selected State.
  5. If you choose more than one State, then the drill down should be disabled.
  6. Once you are in the City view, you should see one dot for each city. For example, if you click on Washington, then the City view should have 40 marks (one for each City).
  7. At the City level, the user should be able to click on the white space in the chart to go back to the State level.
  8. At the City level, no cities should be highlighted.
  9. You must match the tooltips. They show the State, Sales and Profit when each dot is a State. The tooltip shows the City, Sales and Profit when each dot is a City.
  10. The title and subtitle should change depending on if the view is State level or City level.
  11. Each dot should be colored by profit ratio for the State or City.
  12. The view should be 600x650.

Be sure to tweet an image of your work and a Tableau Public link and tag @EmmaWhyte and @VizWizBI. Good luck! I guarantee you'll learn a lot this week.


  1. hi, this workout was great for LODs :) I have a query regarding the option I saw in your workbook which I was not able to re-create at my end.

    on right-clicking on the "Show Cities" details mark I saw an option "show hidden data" which I was not able to re-create at my end. I had to specifically apply a filter on this calc to view on Values which are "True". And still didn't see this option anywhere on my workbook.

    Could you please explain how did you filter the data at your end as the filter was not present on the Filters section too.

    TA :)

    1. I'm not sure I did anything special there. Sorry!

  2. Matching the tooltips - at State level your tooltip shows the sum of sales for the max city in each state - should we be calculating the state sum instead?

    1. Yes that’s how it should work. I’ll have to check it. I thought it was showing state level.