How to Create a Hierarchy in Power BI (Explained Simply)

Written by co-founder Kasper Langmann, Microsoft Office Specialist.

Power BI data hierarchy lets you organize data in a way where one element is ranked over the other. 

By default, Power BI detects your data and organizes date-related data hierarchy automatically.

But what if you would like to create your own data hierarchy? What if that data isn’t related to dates?

In this article, we’ll show you the simple steps in creating a hierarchy in Power BI.

Kasper Langmann, Co-founder of Spreadsheeto

Let’s go! 👑

*This tutorial is for Power BI Online (also called "Power BI Service"). If you have Power BI Desktop, then click here and go to the online version.

Introduction to Power BI Hierarchy

You probably know this already but a hierarchy is a system where elements or entities are ranked over the other.

In Power BI, hierarchy is commonly seen in date and time data.

Here’s an example of a Power BI hierarchy:

Hierarchies in Power BI can be spotted easily because of the hierarchy icon.

Also, hierarchies aren’t only limited to dates.

For example, you could put a hierarchy on production information:

  • Product Category
  • Product Subcategory
  • Color
  • Product

So why are hierarchies important in Power BI?

Simple. Data hierarchy allows you to drill up or down on your visual and reveal additional details.

Take a look at hierarchy and drill through in action:

A drill through in Power BI means filtering your data with respect to the hierarchy to explore in-depth details on that data.

Kasper Langmann, Co-founder of Spreadsheeto

Creating a Hierarchy in Power BI

Fortunately, it’s super easy to create a hierarchy in Power BI. Depending on the number of elements you would like to add, you can be done in less than 10 seconds!

By the way, if you would like to create a hierarchy, make sure you’re using Power BI Desktop. One of the differences between Power BI Desktop and Service is that in the desktop version, you’re able to access the data behind the visuals and do changes.

For this tutorial, we’ll be using the free retail analysis sample where we created a separate date model to use for this exercise.

To start, go to the data view on your Power BI Desktop:

The data view icon on the left pane of Power BI Desktop

This is the view where you’ll be able to create a hierarchy for your data.

The following columns we would like to include in the hierarchy are:

  • Day
  • Month
  • Quarter
  • Year

To start, select the top-level element.

In our example, the hierarchy would be “Year > Quarter > Month > Day”.

Right-click on that element and selectNew hierarchy’:

That will now create a new hierarchy with only the ‘Year’ element in it.

Congratulations! You just made your first hierarchy in Power BI.

Let’s rename the hierarchy to “Date Hierarchy”. Right-click on the newly created hierarchy and selectRename’:

Renaming the newly created hierarchy

Just enter the new name and hitEnter” on your keyboard.

To put the other elements (Day, Month, Quarter) on the hierarchy, simply drag them to the ‘Date Hierarchy’. All of them will be placed below the ‘Year’.

Kasper Langmann, Co-founder of Spreadsheeto

Another way to do that is by right-clicking the element, then ‘Add to hierarchy’, and select the name of that hierarchy (‘Date Hierarchy’):

Adding the other elements to include them on the hierarchy

After that, you would see the elements you added below the top-level element (‘Year’):

The ‘Date Hierarchy’ with the other elements added

Easy, right? 👍

Changing the Hierarchy Levels

The problem is:

The arrangement of the elements is wrong. Like in our example, ‘Quarter’ should come before ‘Month’ and ‘Day’.

To change the hierarchy level, right-click on that element and choose to either move up or down.

Let’s move ‘Quarter’ to the top:

Finish moving everything until you get the right order.

Wrapping things up…

As you can see, creating a hierarchy in Power BI is simple and straight to the point. You don’t even need technical knowledge to do it. The important thing is you know your data.

If you’re confused, just follow the exact steps in this article. After one pass-through, you won’t be able to forget because of its simplicity. 😊

Kasper Langmann, Co-founder of Spreadsheeto