While working on a Tableau report, I wanted to show the data at different levels without creating multiple sheets. For example, I wanted to see sales at the Category level first, and then drill down into Sub-Category, Region or even Product details, all in the same table.
To create the table view according to this, I created a drill-down table in Tableau.
In this tutorial, I will explain the two ways to create a drill-down table in Tableau to visualise data at multiple levels.
Tableau Drill Down Table
In Tableau, a drill-down table allows us to set up a high-level view of our data, such as sales by Region, and then click or expand to see more details, such as Category, Sub-Category, or Product.
Using a drill-down table in Tableau, we can see a detailed view of the data in a single worksheet. Although there is no built-in feature of a “drill-down” table in Tableau, we can create the drill-down table through techniques like creating hierarchies or using sets and parameter actions to navigate through data levels.
Create a Drill Down Table in Tableau
In Tableau, there are two methods for creating a drill-down table. In the examples below, I will explain the two methods with a use case scenario.
Method-1: Create a Drill Down Table in Tableau Using Hierarchy
In this example, I will create a drill-down table using the Hierarchy. The order of hierarchy will be Region > Segment> Order date.
Now, connect the Tableau with the Superstore dataset, and follow the steps below to create a drill-down table in Tableau.
- In this example, I am using the dimension ‘Region,’ which is already added under the hierarchy of ‘Location.’ For this, click on the Region and duplicate it, then give another name to it, such as Sales Region.
- Now, click on the Sales Region dropdown in the Data pane and select Create Hierarchy.
- Enter the name for the hierarchical field, such as Sales Region Drilldown, and click OK.
- Now, to add the Segment and Order date under the hierarchy of this drill-down table, select Segment -> Hierarchy -> Add to Hierarchy -> Sales Region Drilldown.
In the same way, I have added the dimension Order date to the hierarchy in the Sales Region Drilldown.
- To create the chart view, add the created drilldown to the Rows and Sales to the Text card in the Marks section.
- To open the drilldown table, we need to click on the ‘+‘ icon on the Sales Region, and it will open to the other fields that we have added in the hierarchy. In this example, the table will open in the following order: Sales Region > Segment > Order Date.
Now, the table will filter and display according to the fields we expand in the drill-down table.
This way, we can create a drilldown table in Tableau using the hierarchy.
Method-2: Create a Drill Down Table in Tableau Using Parameters
In this example, we will create a drill-down action in which we can see sales by Category, and when we click on a Category, it should drill down to Sub-Category. Tableau already has hierarchies in place, but using the custom drilldown, we can have more control over them.
Now, follow the steps below to create a drill-down table in Tableau using the parameters.
- To create a parameter, click on the dropdown in the Data pane and select Create Parameter.
- Now, label the parameter as Parameter 1, then select the Data type as String and click OK. Here, we have selected the string data type for Category and Sub-Category dimensions.
- In a similar way, create another parameter with the Label as Parameter 2 and the Data type as String.
With these two parameters, we will create a two-level drilldown for the Category field.
- Now, for Parameter level 1, create a calculated field using the formula below.
If [Category]= [Parameter level 1]
THEN [Sub-Category]
ELSE [Category]
ENDThis formula will display the sub-category when the parameter is selected as Parameter level 1.
- Now, to create a parameter action, click on the Worksheet in the navigation bar and select Actions.
- Define the parameter action with the following details.
- Enter the action Name as Parameter Level 1.
- In the Source sheet, select the current sheet.
- In the Target Parameter, select Parameter level 1.
- Select the Source Field as Category from the data source.
- In the “Clearing section,” select “Set Value to” and then click OK.
- To create the table view, add the Sales to columns, then add Category and the calculated field Level 1 to the Columns.
Now, as we click on the Category in the level 1 column, it will expand it to a subcategory. For example, if we click Category Furniture, then the chart will expand the sub-categories of Furniture.
- Now, we will create another calculation to drill down into the sub-category to the Region. For this, create another calculated field, Level 2, using the formula below.
If [Sub-Category]= [Parameter level 2]
THEN [Region]
ELSE ''
END- After this, create a parameter action for the parameter level 2 with the following details.
- Enter the action Name as Parameter Level 2.
- In the Source sheet, select the current sheet.
- In the Target Parameter, select Parameter level 2.
- Select the Source Field as calculated field Level 1.
- In the “Clearing section,” select “Set Value to” and then click OK.
- Now, add the Level 2 calculation to the Rows, and with this we can view the data in the drilldown order of Category > Sub-Category > Region.
This way, we can show a two-level drilldown table in Tableau by using the parameter with parameter actions.
Conclusion
In this Tableau tutorial, we have learned how to create a drill-down table in Tableau using two different methods. In the first method, we created a simple drill-down using a hierarchy with fields.
Then, in the second method, we created a custom drill-down using parameters and parameter actions. With this method, we had more control, as we could decide the exact path of drill down, such as Category -> Sub-Category -> Region.
Using the above two methods, you can create and implement a drill-down table in the Tableau worksheets and view the data from a high-level summary down to nested fields.
You may also like to read:
I am Bijay Kumar, the founder of SalesforceFAQs.com. Having over 10 years of experience working in salesforce technologies for clients across the world (Canada, Australia, United States, United Kingdom, New Zealand, etc.). I am a certified salesforce administrator and expert with experience in developing salesforce applications and projects. My goal is to make it easy for people to learn and use salesforce technologies by providing simple and easy-to-understand solutions. Check out the complete profile on About us.
