Creating Hierarchies in Tableau
Learn how to organize related dimensions into hierarchical structures in Tableau. Understand how to create custom hierarchies, navigate between different levels of detail, and use drill-down and drill-up operations to explore business data effectively.
1. Introduction to Hierarchies in Tableau
A Hierarchy in Tableau is an arrangement of related dimensions in a logical order, from a higher level of detail to a more specific level. Hierarchies allow users to explore data at different levels without manually adding or removing fields from a worksheet.
For example, geographical data can be organized into Country, State, City, and Postal Code. A user can begin with a country and then drill down to view its states, cities, and postal codes.
Example: Geographic Hierarchy
Consider a company that operates in multiple countries. Its sales team wants to analyze sales at country level and then explore individual states and cities.
A hierarchy makes this process easier because the related geographic fields are organized together.
2. Why Do We Use Hierarchies?
In large datasets, information is often available at multiple levels of detail. Hierarchies help users navigate these levels in a structured way and make reports easier to understand.
Key Benefits
- Organize related dimensions into a logical structure.
- Explore data from summary level to detailed level.
- Support drill-down and drill-up analysis.
- Reduce the need to repeatedly drag individual fields into a view.
- Make dashboards and worksheets easier to navigate.
- Support geographical, organizational, product, and date analysis.
3. Understanding Hierarchy Levels
A hierarchy contains multiple levels. The top level generally represents broader information, while the lower levels represent more detailed information.
Example: Company Organization
| Hierarchy Level | Dimension | Example Value |
|---|---|---|
| Level 1 | Department | Engineering |
| Level 2 | Team | Backend Development |
| Level 3 | Employee | Java Developer |
When a manager views employee information, they can begin with department-level information and move down to individual teams and employees.
4. How to Create a Hierarchy in Tableau
Tableau allows you to create a custom hierarchy directly from the Data Pane. You can organize related dimensions by dragging one field onto another.
Step-by-Step Process
- Open Tableau Desktop.
- Connect to your required data source.
- Locate the related dimensions in the Data Pane.
- Identify the field that should be the top level of the hierarchy.
- Right-click that dimension.
- Select Hierarchy and then Create Hierarchy.
- Enter a meaningful hierarchy name.
- Click OK.
- Drag the remaining related dimensions into the hierarchy.
- Arrange the fields in the required order.
Example: Creating a Location Hierarchy
Suppose your dataset contains the following dimensions:
- Country
- State
- City
- Postal Code
Create a hierarchy named Location Hierarchy and arrange the fields in the following order:
Location Hierarchy
Country
State
City
Postal Code
5. Understanding Drill-Down
Drill-down is the process of moving from a higher-level summary to a more detailed level of information. It allows users to explore the details behind aggregated data.
Example: Sales Analysis
Suppose a company wants to analyze sales across different locations. It can begin with total sales by country and then drill down to individual states and cities.
- Place the Location Hierarchy in the worksheet.
- Start with the Country level.
- Click the plus icon next to a country or use the drill-down option.
- Tableau expands the hierarchy to the next level.
- Continue expanding to view cities and postal codes.
6. Understanding Drill-Up
Drill-up is the reverse of drill-down. It allows users to move from a detailed level to a higher-level summary. This is useful when users want to return to an overview after examining individual records.
Example
Suppose you are viewing sales by city. You can drill up to view sales by state and then move further up to view sales by country.
Drill-Up Direction
Postal Code
↑
City
↑
State
↑
Country
Drill-up reduces the level of detail and provides a broader view of the data.
7. Expanding and Collapsing Hierarchies
Tableau provides expand and collapse functionality for navigating hierarchy levels in a worksheet.
Expand
Expanding a hierarchy displays the next lower level of detail. For example, expanding a country displays its states.
Collapse
Collapsing a hierarchy hides the lower-level details and returns the view to a higher level.
| Operation | Purpose | Example |
|---|---|---|
| Expand | Display more detailed hierarchy levels. | Country to State. |
| Collapse | Return to a broader hierarchy level. | State to Country. |
| Drill Down | Explore lower-level information. | State to City. |
| Drill Up | Explore higher-level information. | City to State. |
8. Working with Date Hierarchies
Tableau supports date hierarchies that help users analyze time-based information at different levels, such as year, quarter, month, and day.
When you use a date field in a worksheet, Tableau can provide date levels that allow you to change the level of time detail shown in the visualization.
Example: Sales by Date
Year
└── Quarter
└── Month
└── Day
A sales manager can first review yearly sales, then explore quarterly performance, and finally inspect individual months or days.
9. Common Business Hierarchy Examples
Hierarchies are not limited to geographical or date fields. They can be created for different business requirements by organizing related dimensions.
| Hierarchy Type | Level 1 | Level 2 | Level 3 | Level 4 |
|---|---|---|---|---|
| Geographic | Country | State | City | Postal Code |
| Product | Category | Sub-Category | Product | Product ID |
| Organization | Company | Department | Team | Employee |
| Date | Year | Quarter | Month | Day |
10. Editing and Managing Hierarchies
After creating a hierarchy, you can modify its structure according to your reporting requirements.
Adding a Field
- Locate the required dimension in the Data Pane.
- Drag the dimension into the existing hierarchy.
- Place it at the required hierarchy level.
Reordering Fields
Drag hierarchy fields up or down within the hierarchy to change their order. The order should represent the logical movement from broader information to more detailed information.
Removing a Field
- Expand the hierarchy in the Data Pane.
- Right-click the field you want to remove.
- Choose Remove from Hierarchy.
Renaming a Hierarchy
Right-click the hierarchy name in the Data Pane, select Rename, and enter a meaningful name.
Deleting a Hierarchy
If a hierarchy is no longer required, right-click its name and select Delete. Review your worksheets before deleting a hierarchy that is already in use.
11. Difference Between Groups, Sets, and Hierarchies
Groups, Sets, and Hierarchies help organize and analyze data, but they perform different functions in Tableau.
| Feature | Groups | Sets | Hierarchies |
|---|---|---|---|
| Main Purpose | Combine related dimension members. | Create a subset of dimension members. | Organize related dimensions into levels. |
| Structure | Grouped categories. | IN and OUT membership. | Parent-child levels of detail. |
| Typical Usage | Combine product categories. | Find top 10 customers. | Drill from country to city. |
| Dynamic Behavior | Depends on how the group is maintained. | Supports rule-based membership. | Provides navigation across levels. |
| Example | Combine multiple states into a region. | High-profit products. | Category to Sub-Category to Product. |
12. Practical Example: Product Sales Hierarchy
Let's create a Product Hierarchy for a retail business. The company wants to analyze sales from product categories down to individual products.
Sample Dataset Fields
- Category
- Sub-Category
- Product Name
- Sales
- Profit
Step 1: Create Product Hierarchy
- Locate Category in the Data Pane.
- Right-click Category.
- Select Hierarchy and Create Hierarchy.
- Name the hierarchy Product Hierarchy.
- Drag Sub-Category into the hierarchy.
- Drag Product Name into the hierarchy.
Step 2: Build a Sales Visualization
- Open a new worksheet.
- Drag Product Hierarchy to Rows.
- Drag Sales to Columns.
- Choose Bar Chart from the Show Me panel if required.
- Review sales at the category level.
Step 3: Drill Down
- Expand a product category.
- View its sub-categories.
- Expand a sub-category.
- View individual product sales.
Expected Hierarchy Structure
Product Hierarchy
Category
Sub-Category
Product Name
The sales manager can now move from category-level performance to individual product performance without rebuilding the worksheet.
13. Best Practices for Creating Hierarchies
- Arrange fields from general information to detailed information.
- Use meaningful hierarchy names.
- Include only logically related dimensions.
- Keep hierarchy levels easy to understand.
- Use date hierarchies for time-based analysis.
- Use geographic hierarchies for location-based reporting.
- Test drill-down and drill-up behavior in worksheets.
- Avoid unnecessary hierarchy levels.
- Review existing worksheets before changing hierarchy structures.
14. Common Mistakes and Solutions
| Problem | Possible Reason | Solution |
|---|---|---|
| Incorrect hierarchy order | Fields are arranged in the wrong sequence. | Reorder fields from general to detailed. |
| Drill-down not showing expected data | Incorrect or unrelated dimensions are used. | Review the hierarchy structure and data relationships. |
| Duplicate or confusing levels | Similar dimensions are added unnecessarily. | Remove unnecessary fields. |
| Hierarchy difficult to understand | Unclear field names or hierarchy names. | Use meaningful names and logical ordering. |
| Unexpected worksheet results | Changes affect fields used in existing views. | Review and validate affected worksheets. |
Summary
Hierarchies in Tableau organize related dimensions into a logical structure that moves from higher-level information to more detailed information. They support drill-down, drill-up, expanding, and collapsing operations, allowing users to explore data without repeatedly rebuilding worksheets. Custom hierarchies can be created using the Data Pane by arranging related dimensions in the required order. Common examples include geographic, product, organizational, and date hierarchies. By creating meaningful hierarchies and maintaining their logical structure, analysts can make dashboards easier to navigate and perform detailed business analysis more efficiently.