Do you know how to create hierarchy in Power BI? Okay, no problem.
In this Power BI tutorial, I will explain what a hierarchy is in Power BI, the purpose of using a hierarchy in Power BI, and how to create a hierarchy in Power BI.
Also, I will guide you on how to use a hierarchy in Power BI and explain the concept of a default date hierarchy in Power BI.
Power BI Hierarchy
- In Power BI, a hierarchy organizes and structures data in a nested or tiered manner.
- Power BI hierarchy allows you to group related fields or attributes in a tree-like structure, typically with a parent-child relationship.
- Power BI hierarchies make it easier to navigate and analyze data, providing a more organized and intuitive way to drill down into specific levels of detail.
Power BI Hierarchy looks like the screenshot below:
What is the use of a Hierarchy in Power BI
- Hierarchies make it simpler to organize data in Power BI.
- They let users explore data more closely in Power BI by moving from general categories to specific details, offering a more detailed data view.
- Power BI users can efficiently analyze data by focusing on specific levels of detail.
How to create Hierarchy in Power BI
Now, I will tell you how to create a hierarchy in Power BI in simple steps:
I have a SharePoint list that I am using for this tutorial in Power BI Report.
| Columns | Data Types |
|---|---|
| Order Date | Date and time |
| Country/Region | Single line of text |
| City | Single line of text |
| Region | Single line of text |
| Category | Single line of text |
| Sub-Category | Single line of text |
| Product Name | Single line of text |
| Sales | Currency |
| Quantity | Number |
1. Open Power BI Desktop and load data using the Get data option. Then, we can see the data set in the Data Panel.
I want to create a hierarchy, so I have a Category as level one and then a Subcategory.
2. Right-click on the field you wish to form a hierarchy, in my case, Category. Then select the Create hierarchy.
After that, you will see a new hierarchy created by Category Hierarchy.
3. Now, I will add a Sub-Category to the new hierarchy. So do this: Right-click on the Sub-Category -> Add to hierarchy -> Category Hierarchy.
4. Then, in the same way, add the Product Name to the Category Hierarchy. Then, our Category Hierarchy looks like the screenshot below.
This way, you can create a hierarchy in Power BI.
How to use a hierarchy in Power BI
I hope you create a hierarchy using the above steps. Now, I will show you how to use a hierarchy in Power BI
1. In the visualization Panel, click the Stacked column chart. Then, drag Category Hierarchy into the X-axis and Sales into the Y-axis field.
In this way, You can use hierarchy in Power BI.
Default Date hierarchy in Power BI
Now, we see the default date hierarchy in Power BI.
When I connect the SharePoint List in Power BI Desktop, the Order Date automatically hierarchy in the Data panel is called the Default Date Hierarchy, which includes Year, Quarter, Month, and Day. Check out the screenshot below
This occurs when you have a Date data type in your dataset.
Conclusion
I hope you followed the above steps to create a hierarchy in Power BI. In this article, I explained what a hierarchy is in Power BI, the purpose of using a hierarchy, and the steps to create a hierarchy in Power BI.
Also, we discussed how to use a hierarchy in Power BI and the concept of the default date hierarchy in Power BI.
Additionally, you may like some more Power BI tutorials:
- Create a Date Hierarchy in Power BI
- Power BI DAX Query View
- Sort Month Names in Power BI Slicer
- Export Matrix to Excel in Power BI
Preeti Sahu is an expert in Power Apps and has over six years of experience working with SharePoint Online and the Power Platform. She is the co-author of Microsoft Power Platform: A Deep Dive book. As a Power Platform developer, she has worked on developing various tools using Power Apps and Power Automate. She also makes Microsoft 365 videos and shares them on YouTube.
