Graphed LogoGraphed
Graphed LogoGraphed
FeaturesUse CasesIntegrationsPricing

How to Create Hierarchies in Power BI

Cody Schneider••8 min read

Drilling down into your data should be an intuitive, aha moment, not a frustrating series of clicks and filters. Power BI hierarchies are a simple but incredibly powerful feature that transforms flat, static charts into interactive, layered stories. Instead of showing just yearly sales, you can let your team explore from the year, down to the quarter, the month, and even the individual day with a single click. This guide will show you exactly how to build these drill-down experiences in your reports.

GraphedGraphed

Your AI Data Analyst to Create Live Dashboards

Connect your data sources and let AI build beautiful, real-time dashboards for you in seconds.

Watch Graphed demo video

We’ll cover the two primary methods for creating hierarchies: the super-fast drag-and-drop technique for quick analysis and the more structured Model View method for building robust, reusable data models. Let's get your data organized.

Why Bother Creating Hierarchies in Power BI?

Before we jump into the "how," let’s quickly cover the "why." A hierarchy is essentially a logical, parent-child relationship between related data fields. Think of it like a set of nested folders on your computer: you click the "2024" folder to see the month folders inside, and then click a month folder to see the day folders.

In Power BI, this structure provides several key benefits:

  • Powerful Drill-Down Functionality: This is the star of the show. Hierarchies enable users to start with a high-level view (like total sales by product category) and seamlessly drill down into the 'why' behind the numbers (like sales by sub-category and then by individual product).
  • Improved Report Organization: In the Fields pane, instead of seeing a long, confusing list of columns, hierarchies bundle related fields together. A single “Product Hierarchy” is much cleaner than having separate p_category, p_subcategory, and p_name fields floating around.
  • An Intuitive User Experience: People naturally think in hierarchies. A report that lets your stakeholders explore Country > State > City feels more natural and is easier for non-technical users to understand and analyze on their own.

Free PDF Guide

AI for Data Analysis Crash Course

Learn how to get AI to do data analysis for you — the best tools, prompts, and workflows to go from raw data to insights without writing a single line of code.

Common Examples of Hierarchies:

  • Time: Year → Quarter → Month → Day
  • Geography: Country → Region → State → City
  • Product: Category → Sub-Category → Product Name → SKU
  • Organizational: Department → Team → Employee

Method 1: The Quick Drag-and-Drop Hierarchy

This is the fastest and most common way to create a hierarchy for immediate use within a single report. It’s perfect when you're exploring the data and need to build a drill-down path on the fly.

Step-by-Step Guide

We’ll use a common sales scenario: creating a hierarchy for Product Category, Sub-Category, and Product Name.

  1. Locate Your Fields: In the Fields pane on the right side of the Power BI window, identify the fields you want to group together. Let's say we have Category, Sub-Category, and Product-Name.
  2. Start with the Parent: Identify the field that is at the highest level of your hierarchy. In our case, this is Category.
  3. Drag and Drop: Click on the next level down in your hierarchy, Sub-Category, and drag it directly on top of the Category field. Don't let go until Category is highlighted.
  4. Confirmation: When you release the mouse, Power BI will automatically create a new item in your Fields pane named "Category Hierarchy." You'll see Category and Sub-Category neatly nested inside.
  5. Add More Levels: Now, find the Product-Name field and drag it on top of your newly created "Category Hierarchy." It will be added as the third level.
  6. (Optional) Rename the Hierarchy: A clear name is always better. Right-click on your hierarchy and select Rename. Let’s change it to “Product Hierarchy.”

That’s it! In less than 30 seconds, you've created a functional hierarchy ready to be used in your visuals.

Using Your New Hierarchy in a Visual

Once created, using the hierarchy is simple:

  1. Select a visual, like a clustered column chart.
  2. Drag your entire “Product Hierarchy” from the Fields pane into the X-axis field well.
  3. Drag a measure, like Total Sales, into the Y-axis field well.

At first, your chart will only show data by the top level (Category). But now, you will see a set of new drill-down icons in the top-right corner of your chart. These are your new superpowers:

  • Enable Drill Down (Downward arrow): This is the main "on/off" switch. Click this to turn on drill mode. Once it's on, clicking on a specific bar in your chart (e.g., the 'Accessories' category) will drill down to show the sub-categories within that selection.
  • Go to the Next Level (Double downward arrow fork): Clicking this will expand the entire hierarchy down one level. For example, it would show values for all Sub-Categories, not just those within one selected Category.
  • Expand All Down One Level (Double downward arrow): This fan-favorite will show all levels of the hierarchy at once on the axis. For example, it will display “Accessories – Helmets”, “Accessories – Gloves”, etc., concatenating the parent and child labels.
GraphedGraphed

Your AI Data Analyst to Create Live Dashboards

Connect your data sources and let AI build beautiful, real-time dashboards for you in seconds.

Watch Graphed demo video

Method 2: Building Hierarchies in the Model View

While the drag-and-drop method is great for speed, the Model View method is a more formal and robust way to define your data relationships. This is considered a best practice when you're building a more permanent, shareable data model that others on your team might use.

Step-by-Step Guide

Let's create the same "Product Hierarchy" using this method.

  1. Switch to Model View: On the far left of the Power BI window, click on the Model View icon (it looks like a diagram of connected tables).
  2. Find Your Table: Find the table containing your product fields. Let's call it DimProduct.
  3. Right-Click to Create: In the DimProduct table, find the top-level field, Category. Right-click on it and select Create hierarchy.
  4. Hierarchy is Created: Power BI immediately creates the hierarchy in the Fields pane and inside a hierarchy folder at the bottom of your table in Model View. It will be named "Category Hierarchy" by default and will contain only Category to start.
  5. Add the Other Levels: Now, find the Sub-Category field. You can either drag it into the hierarchy folder you just created, or right-click it and choose Add to hierarchy > Product Hierarchy. Repeat this process for Product-Name.
  6. Arrange the Levels: In a hierarchy, order is everything. You can click and drag the fields within the hierarchy folder (in either Model View or the Report View's Fields Pane) to ensure it flows correctly from Category to Sub-Category to Product-Name.

When Should You Use the Model View Method?

This approach is preferred in a few scenarios:

  • When building a central, formal dataset to be shared with multiple reports or users.
  • When you want your data model to be more self-documenting for other developers.
  • For core business hierarchies (Time, Geography, Product) that will be used consistently across the business.

A Quick Note on Date Hierarchies

Power BI is smart about dates. Whenever you drag a date field into a report, Power BI's "Auto Date/Time" feature often creates an automatic date hierarchy for you on the fly. You'll see it break the date down into Year, Quarter, Month, and Day without you having to do a thing.

This is incredibly helpful, but if you have your own specific date or fiscal calendar table (which is a best practice), you can turn this feature off. You can find it under File > Options and settings > Options > Data Load > Auto date/time. Unchecking this box will give you more control, forcing you to use hierarchies from a dedicated calendar table.

Free PDF Guide

AI for Data Analysis Crash Course

Learn how to get AI to do data analysis for you — the best tools, prompts, and workflows to go from raw data to insights without writing a single line of code.

Tips for Effective Power BI Hierarchies

Creating hierarchies is easy, but creating good hierarchies that improve a report takes a little more thought.

  • Order Matters Most: Make sure your hierarchy levels are in a logical sequence. It makes no sense to have City > Country. Double-check your ordering before you publish.
  • Keep it Lean: Don't create hierarchies with ten levels. More than four or five levels can get cluttered and confusing for the end-user to navigate. Stick to what provides the most analytical value.
  • Use Clear Naming: Hierarchy1 is not a helpful name. Name your hierarchies (Geography, Product Rollup) and your fields clearly so users know exactly what they're looking at.
  • The "Sort by Column" Trick: By default, Power BI sorts text fields alphabetically. This is a big problem for month names ("April," "August," "December"). Use a companion Month Number (1, 2, 3...) column. You can then select the Month Name column in the Data View, go to the Column tools ribbon, and click Sort by column > Month Number. This ensures your charts display months in chronological order, and your hierarchy will respect a sane order.

Final Thoughts

Building hierarchies into your Power BI reports is a fundamental skill that takes your work from just showing data to enabling true discovery. Whether you use the quick drag-and-drop method for fast analysis or the more structured Model View, you’re making your dashboards infinitely more valuable and intuitive for anyone who uses them.

Mastering tools like Power BI is a fantastic skill, but we know creating effective dashboards can still feel like manual busywork that consumes your day. At Graphed, we created a way to skip that manual process entirely. You can connect your marketing and sales data sources in seconds, and then use simple, natural language to get answers. By just asking, “show me a dashboard of last month’s Shopify revenue by marketing channel,” you can get a live, interactive dashboard built for you instantly. Our goal is to let you jump straight from asking questions to finding insights, no manual charting required.

Related Articles