Selectively expand hierarchies by using a parameter

Tableau enables you to create hierarchies of the different dimensions, which you can use for drilling up and down in order to see and analyze your data at different levels. As an example one can show the CO2 emission at either state- or sector level through a hierarchy, which is illustrated below.

Usually this is very helpful in understanding your data, but sometimes it can be beneficial to only expand a single group instead of drilling down all states to sector level. As in this example, one could be interested in solely drilling down a single state to sector level in order to analyze this, but still maintain the overview of the CO2-emission on state level for other non-selected states.

Below I have shown traditional drill down versus my selective-expanding-solution.

Traditional drill down

Selective expanding solution

The question remains: how to do it? The solution is actually fairly simple. All that we need is to create a parameter, which in turn can be used in a calculated field such that only the selected state will be split on sector level.

Creating the parameter

  1. Right-click in the data select create parameter.

  2. In the Create Parameter dialog box, do the following, and the click OK

  • Give the parameter a name

  • Set Data type to String

  • For Allowable values select List

  • Press Add from field and select State

  • Now reassign numbers for the different states in the Value column and press type in the state names in the Display As

    The numbers are used for making the calculated field later on, a bit less complicated. In this case a row with the Value of “1” and Display As “All” has been created so one can maintain the option of drilling down all states in one click. You should end up with a parameter dialog box like this:

  • Press OK

  1. Right-click the new parameter and select Show Parameter Control

    One will notice that the parameter does not change the level of detail to sector level yet, why one has to create a new calculated field that can be used in combination with the parameter to control the drilling up and down. Below shows a step-by-step guide to creating the calculated field and the code needed to include the parameter in the calculation.

Create a new calculated field

  1. Right click in the Data window and select Create Calculated Field.

  2. Name the Calculated Field.

  3. In the Calculated Field dialog box, write the code given below.

  4. Press OK

Code descriptionThe code is defined by using the the newly created parameter, which in this case was named Drill Down State. Depending on the value that the user selects it will return different values, i.e. it will drill down from state level to sector level. As written in the code if one chooses e.g. “2” (set to be displayed as Alabama in the Parameter Card), Tableau returns the sector values, which has Alabama to equal state and otherwise returns a blank field, indicated by the double “” following the ELSE statement. If this is not done Tableau returns null values in the calculated field column. Try to experiment with this and see for yourself.

Final stepNow almost everything is in place and all that is left is to drag the calculated field to the rows column as shown below. Now one can use the parameter card to drill down either all states to the sector level or a specific selected one.

In this specific example, only the states of Alabama and Alaska was defined such that it is possible to drill down these selectively. Thus, the drawback of this way of selectively drilling up and down is that, in the case where you have a lot of different values, it can be a rather comprehensive job to define all.

  • Hope it has been useful!

Back to blog

Subscribe for updates