Graphed LogoGraphed
Login

How to Create a Hierarchy in Power BI

Cody Schneider•

Building an effective Power BI report isn't just about showing data, it's about telling a story and allowing users to explore that data on their own. One of the best ways to enable this exploration is by creating hierarchies. This article will walk you through exactly what hierarchies are, why they are so valuable, and how you can create and use them step-by-step to make your reports dramatically more interactive and intuitive.

What is a Hierarchy in Power BI and Why Should You Use One?

In simple terms, a hierarchy in Power BI is a logical grouping of related fields that create a parent-child relationship for drill-down analysis. Think of it as a pre-defined path that lets you navigate from a high-level, summary view of your data down to the granular details.

For example, instead of looking at Year, Quarter, Month, and Day as four separate fields, you can combine them into a single "Date Hierarchy." When you use this hierarchy in a chart, you can start by viewing total sales by year, then click a specific year to drill down into its quarters, and then click a quarter to see the months within it.

Common examples include:

  • Time Hierarchy: Year → Quarter → Month → Day

  • Geography Hierarchy: Country → State → City → Postal Code

  • Product Hierarchy: Category → Sub-Category → Product Name

So, why bother setting them up? The benefits are significant:

  • Improves User Experience: Instead of cluttering your visuals (and the Fields pane) with numerous individual fields, you provide a single, logical item. This makes your reports cleaner, more organized, and easier for others to understand and use.

  • Enables Powerful Drill-Down Analysis: This is the star feature. Hierarchies are the key to unlocking Power BI's built-in drill-down and drill-up capabilities. Users can effortlessly explore data without needing separate tabs or different charts for each level of detail.

  • Simplifies Your Data Model: A well-organized Fields pane is easier to navigate. Grouping related columns into hierarchies tidies up your model, making it faster to find what you need while building reports.

  • Saves Time for Report-Builders: You can drag a single hierarchy into a visual instead of dragging and arranging three, four, or more individual fields. It’s a small efficiency that adds up across complex reports.

How to Create a Hierarchy in Power BI: A Step-by-Step Guide

Creating hierarchies is a fundamental skill in Power BI, and thankfully, it's quite straightforward. You can primarily do it in two ways inside Power BI Desktop, either in the Report view or the Data view.

Method 1: The Simple Drag-and-Drop Approach

This is the quickest and most intuitive way to build a hierarchy. Let's create a product hierarchy with Category, Sub-Category, and Product Model.

  1. Open the Fields Pane: On the right side of your Power BI Desktop canvas, locate the Fields pane where all your tables and columns are listed.

  2. Identify Your Fields: Find the fields you want to group. For our example, let’s assume they are ProductCategory, ProductSubCategory, and ProductModel.

  3. Start with the Highest Level: Find the highest-level field, which is ProductSubCategory in this case. Click and hold the ProductSubCategory field and drag it directly on top of the ProductCategory field.

  4. Hierarchy is Created: When you release the mouse button, Power BI will automatically create a hierarchy. You'll see a new field with a special hierarchy icon, likely named ProductCategory Hierarchy. It will contain both ProductCategory and ProductSubCategory.

  5. Add More Levels: Now, find the next field in the sequence, ProductModel. Drag ProductModel and drop it directly onto the newly created ProductCategory Hierarchy. It will be added as the third level.

  6. Rename the Hierarchy: The default name might be okay, but it's good practice to give it a clearer name. Right-click the hierarchy and select Rename. A simple, descriptive name like Products or Product Hierarchy works well.

That's it! You've successfully created a hierarchy that you can now use in your visuals.

Method 2: Using the 'Create Hierarchy' Menu

This method feels a bit more formal and gives you an alternative if you find drag-and-drop a bit finicky.

  1. Start with the Top-Level Field: In the Fields pane, find the field that will be at the top of your hierarchy (e.g., Country).

  2. Right-Click to Create: Right-click on the field (or click the three dots ... that appear when you hover over it) and select Create hierarchy from the context menu.

  3. A New Hierarchy Appears: Power BI will create a new hierarchy named Country Hierarchy containing only the Country field.

  4. Add Other Fields to the Hierarchy: Now find the next levels for your hierarchy (e.g., State and City). For each field:

    • Right-click the field (State).

    • Hover over Add to hierarchy in the context menu.

    • Select the hierarchy you just created (Country Hierarchy).

    Repeat this step for the City field to complete your geography hierarchy.

Managing and Editing Hierarchies in Power BI

Your needs might change, or you might make a mistake during setup. Fortunately, editing hierarchies is just as easy as creating them.

Reordering Levels in a Hierarchy

The order of fields in a hierarchy is critical for the drill-down logic. If you added fields in the wrong order — for example, putting City before State — you can fix it easily. In the Fields pane, simply expand the hierarchy and drag the fields up or down within the hierarchy to place them in the correct sequence.

Adding or Removing Fields

Need to add a new level of detail, like County? Just right-click the County field, select Add to hierarchy, and choose your existing geography hierarchy. To remove a field, expand the hierarchy, right-click the field you want to remove, and select Remove from hierarchy. This action only removes it from the group, it doesn't delete the field from your data model.

Hiding the Original Fields

Once you’ve built a hierarchy, the original individual fields (e.g., Year, Quarter, Month) can clutter the Fields pane. To create a cleaner authoring experience for yourself and others, it’s a great practice to hide them. Right-click each of the original component fields and select Hide in report view. This encourages everyone to use the official hierarchy and prevents accidental use of the wrong fields.

Using Hierarchies in Your Power BI Visuals for Drill-Down Analysis

Now for the payoff. With your hierarchy created, using it to enable exploration is simple.

  1. Add a Visual: Select a visual like a clustered column chart or a matrix to your report canvas.

  2. Drag in the Hierarchy: From the Fields pane, drag your entire Product Hierarchy into the Axis field well of your column chart. Drag a measure, like Total Sales, into the Y-axis field well. Immediately, your chart will display sales by ProductCategory.

  3. Enable Drill-Down: In the top right corner of the visual, you'll see a set of arrow icons. These control the drill-down functionality.

    • Drill Down (Single Arrow Down): Click this icon to turn Drill Mode on. Now, when you click on a specific bar — for example, the "Accessories" bar — the visual will drill down to show the ProductSubCategory sales only within Accessories.

    • Go to the next level in the hierarchy (Forked Double Arrows Down): Clicking this expands all categories at once. Instead of seeing details for just one category, you'll see the sub-categories for all of them.

    • Drill Up (Single Arrow Up): This takes you back up one level in the hierarchy.

  4. Interacting with Drill-Down: By providing these interactive drill-down paths, you transform a static report into an exploratory tool, empowering business users to answer their own questions and find insights without asking for new versions of the report.

Best Practices for Creating Effective Hierarchies

  • Define a Logical Order: Ensure your levels are ordered from the broadest category to the most specific (e.g., Country → State → City). An illogical order makes the drill-down experience confusing.

  • Use a Proper Calendar Table: While Power BI can auto-generate a date hierarchy for any date field, it's a strongly recommended best practice to create your own dedicated calendar table in your data model. Creating your date hierarchy from a proper calendar table provides more control, better performance, and fiscal calendar flexibility.

  • Keep Naming Conventions Clean: Give your hierarchies meaningful names like Geography or Product Hierarchy instead of the default Category Hierarchy. This makes the model more intuitive.

  • Mind the Granularity: Don't create hierarchies with an excessive number of levels unless it provides real business value. A 10-level drill-down can become clumsy for the end-user.

Final Thoughts

Incorporating hierarchies into your Power BI reports is a fundamental technique for transforming them from simple charts into interactive analytical tools. By grouping related fields, you not only neaten your data model but also unlock the powerful and intuitive drill-down features that allow users to explore data from a high-level overview down to the finest detail.

Ultimately, the goal is always to get insights from your data faster and more efficiently. While learning features like Power BI hierarchies is an excellent way to improve your reports, sometimes you just need to skip the setup and get a direct answer. At Graphed we’ve made this possible by letting you connect your data sources — like Google Analytics, Shopify, and Salesforce — and simply ask questions in plain English. Instead of building the visuals yourself, you can ask, "Show me my sales by product category and sub-category for the last quarter," and we instantly create the report for you.