Creating a Hierarchy in Power BI: Unveiling Essential Steps

Creating a hierarchy in Power BI is a pivotal skill for anyone seeking to enhance their data management and visualization capabilities.

Effective management and delegation of tasks are paramount to the smooth operation of companies.

Similar to organizational hierarchies, data management and analysis depend on structured hierarchies for efficient utilization of information.

In Power BI, data is organized within an organization based on ranks, roles, and user-specific needs.

It establishes an order that enables data to flow seamlessly from high-level categories to low-level categories.

Power BI offers a unique feature known as hierarchies, which essentially creates a parent-child relationship within your data.

Within this structure, upper-tier classifications serve as the “guardians,” overseeing lower-tier classifications referred to as the “offspring.” This arrangement allows an intuitive, organized, and user-specific data access experience.

In this article, we will guide you on creating a hierarchy in Power BI. We begin by defining what a Power BI hierarchy is.

We’ll also learn the benefits of using hierarchies in Power BI. We’ll also know when to use hierarchies in Power BI.

Table of Content:

1. What is a Power BI Hierarchy?
2. Creating a Hierarchy in Power BI: When and Why?
3. Types of Hierarchies in Power BI
4. How to Use Hierarchies to Create More Informative and Insightful Visualizations?
5. What are the Benefits of Creating a Hierarchy in Power BI?
6. Wrap Up

What is a Power BI Hierarchy?

In Power BI, a hierarchy is a structured data arrangement that allows users to analyze data at different levels. This facilitates a step-by-step descent from higher to lower data levels.

Creating a hierarchy in Power BI, using the Power BI connector, is a dynamic process that mirrors a parent-child relationship within data management.

This data structure mirrors a parent-child relationship, with lower-level management deriving information from higher levels. Hierarchies provide a tree-like organization for data.

For instance, a hierarchy in Power BI could be “Dates: Year > Quarter > Month > Day.

Establishing hierarchies in Power BI streamlines data drilling, and visualization aids, and enables in-depth analysis.

Creating a Hierarchy in Power BI: When and Why?

Power BI’s hierarchies serve as a robust tool that enhances better data exploration and understanding for users.

Here are some instances in which you should consider creating a hierarchy in Power BI:

You have data that is naturally hierarchical

The use of hierarchies in Power BI is best when your data exhibits a hierarchical structure. For example, data on geographic regions and dates inherently exhibits hierarchical structures.

When your users need to explore data at various levels in detail

In organizations, users at different levels may need to examine data at various levels of detail.

Power BI hierarchies facilitate seamless data exploration, improving communication and information flow through drill-up and drill-down capabilities.

Additionally, hierarchies can help restrict data access to those who should have it, ensuring data security.

When you have data with changing structures

Creating a hierarchy in Power BI offers significant advantages, especially when dealing with dynamic datasets that undergo structural changes over time. In such scenarios, hierarchies play a crucial role in simplifying report maintenance.

Instead of altering individual visuals, users can seamlessly adjust the hierarchy definition based on evolving data structures.

This flexibility is exceptionally valuable, providing an efficient way to adapt to changes in your dataset without the need for extensive modifications.

When you have complex data

Hierarchies simplify data navigation and exploration, making them well-suited for complex data structures or large datasets.

This enables users to find relevant information efficiently, reducing the cognitive load associated with understanding complex data.

When you want to enhance user activity

Hierarchies provide a means of allowing users to customize their data analysis. This customization promotes user exploration and expansion of data, allowing a focus on specific areas of interest.

Hierarchies empower users to customize their data analysis, enhancing user engagement and data-driven decision-making.

When you need to consolidate data

Creating a hierarchy in Power BI is pivotal for the consolidation of data, particularly when aiming to generate user-friendly reports and dashboards. They enable the presentation of multiple levels of detail in a single visual.

This reduces clutter in your reports and ensures that users can quickly access the information they require.

When choosing a hierarchy, it’s crucial to evaluate your data, business requirements, and information flow.

Types of Hierarchies in Power BI

One can create various types of measures in Power BI based on your data and analysis requirements.

Common types of measures in Power BI are:

• DAX Function Measures
• Ratio and Percentage Measures
• Time-Related Measures
• Summarization Measures
• Growth and Variance Measures
• Statistical Measures
• Key Performance Indicators (KPIs)
• Financial Measures
• Custom Measures
• Budget vs. Actual Measures

The measures you create in DAX format depend on your Power BI project’s specific analysis and reporting needs. This allows you to extract valuable insights and generate informative reports and dashboards.

How to Use Hierarchies to Create More Informative and Insightful Visualizations?

In this section dedicated to creating a hierarchy in Power BI, we delve into the process of utilizing hierarchies to fashion more informative and insightful visualizations. We’ll use the Sankey Diagram (Sankey Chart) in the Power BI business dashboard example.

You need at least two tables to create a hierarchy in a Sankey diagram in Power BI:

• The Source Table: This table should contain a column with the values for the starting nodes of the hierarchy. This is the top level.
• Destination Table: This table should contain a column with values for the end nodes of the hierarchy. This is the bottom level.

The Sankey diagram flows from the Source Table values to the Destination Table values.

Additionally, you can include intermediate tables for extra hierarchical levels.

It is generally better practice to import the source and destination data together into a single Power BI dataset rather than separately. This is because there should be a linkage between them.

Note that you might need to use Power BI Desktop to create hierarchies easily. Creating a Sankey diagram follows the same procedure in both Power BI Desktop and Power BI Online.

Let’s first log in to Power BI and upload our dataset.

Stage 1: Logging in to Power BI

• Log in to Power BI.
• Enter your email. Click the “Submit” button.

• Enter your password and click “Sign in.”

• Choose whether to stay signed in.

Stage 2: Creating a Data Set and Selecting the Data Set to Use in Your Sankey Chart

• Click the “Create” option on the left-side menu.
• Select ”Paste or manually enter data.”

• We’ll use the following customer shopping dataset.

Category	Subcategory	Product	Store	Sales
Clothing	Tops	T-Shirt	Store A	15000
Clothing	Tops	T-Shirt	Store B	13000
Clothing	Tops	Jackets	Store C	14500
Clothing	Tops	Jackets	Store B	16000
Electronics	Phones	iPhone	Store C	12000
Electronics	Phones	iPhone	Store A	19000
Electronics	Phones	Android	Store A	18000
Electronics	Phones	Android	Store A	10000o
Electronics	Tablets	iPad	Store B	17000
Electronics	Tablets	iPad	Store A	18500
Electronics	Tablets	Android Tablet	Store B	16000
Electronics	Tablets	Android Tablet	Store A	17050
Clothing	Shoes	Sneakers	Store B	14500
Clothing	Shoes	Boots	Store A	15550
Clothing	Shoes	Sneakers	Store C	1710
Clothing	Shoes	Boots	Store A	1852
Electronics	Headphones	Wireless	Store B	13200
Electronics	Headphones	Wireless	Store A	14500
Electronics	Headphones	Wired	Store A	12100
Electronics	Headphones	Wired	Store C	13150
Electronics	Computers	Laptop	Store B	15000
Electronics	Computers	Laptop	Store B	18000
Electronics	Computers	Desktop	Store A	13000
Electronics	Computers	Desktop	Store C	19000
Clothing	Accessories	Bags	Store A	12500
Clothing	Accessories	Bags	Store A	13250

• Paste the above data table into the “Power Query” window.

• Click on the “Data Hub” option on the left-side menu.
• Power BI populates the data set list. (If you have not created a data set, refer to the Error! Reference source not found section.)
• The data details are shown below:

• Click on the dropdown next to “Explore” under “Discover Business.”
• Select “Create a blank report.”

• You should see the Report Canvas screen below:

Stage 3: Adding the Power BI Sankey Diagram Extension by ChartExpo

• To finish creating our Sankey Diagram, we’ll use an add-in or Power BI visual from AppSource.
• Navigate to the Power BI Visualizations panel.
• Click the ellipsis (…) to import the Power BI Sankey Diagram extension by ChartExpo.

• The following menu opens.
• Select the “Get more visuals” option.
• The following window opens:

• Enter “Sankey Diagram for Power BI by ChartExpo” in the highlighted search box.
• You should see the “Sankey Diagram for Power BI by ChartExpo” in the image below.

• Click the highlighted “Add” button.

• Power BI will add the “Sankey Diagram for Power BI by ChartExpo” in the visualization panel.

Stage 4: Drawing a Sankey Diagram with ChartExpo’s Power BI extension

• Select the “Sankey Diagram for Power BI by ChartExpo” icon in the visualization panel.
• The following window opens in the report section of your dashboard:

• You can resize the visual as needed.
• Go to the right-hand side of your Power BI dashboard.

• You’ll select the fields to use in your Sankey chart here.
• The ChartExpo visual needs to be selected, though.
• Select the fields in the following sequence:
  • Category
  • Product
  • Sales
  • Store
  • Subcategory

• You’ll be asked for a ChartExpo license key or email address.

Stage 5: Activate your ChartExpo Trial or Apply a Subscription Key

• Select the ChartExpo visual. You should see three icons below “Build Visual” in the Visualizations panel.

• Select the middle icon, “Format visual.”
• The visual properties will be populated as shown below.

• If you are a new user,
  • Type in your email under the section titled “Trial Mode”.
  • This should be the email address that you used to subscribe to the ChartExpo add-in. It is where your ChartExpo license key will be sent.
  • Ensure that your email address is valid.
  • Click “Enable Trial.” You’ll get a 7-day trial.

• You should receive a welcome email from ChartExpo.
• The Sankey Diagram you create under the 7-day trial contains the ChartExpo watermark (see below).

• If you have obtained a license key:
  • Enter your license key in the “ChartExpo License Key” textbox in the “License Settings” section (see below).
  • Slide the toggle switch next to “Enable License” to “On.”

• Choose the dataset on your computer.
• Click “Load”

• The data should now be available in the “Data” panel.
• Under the “Data panel” on Power BI Desktop, expand your data, “Sheet 1.”
• Right-click on “Category.”
• Click on the ellipsis (…) at the end of “Category” and choose “Create hierarchy” as below:

• This creates the Category column as the source.

• Right-click on “Subcategory.”
• Click on the ellipsis (…) at the end of “Subcategory.”
• Select “Add to hierarchy.”
• Choose “Category Hierarchy.”
• Subcategory will be added to the Category hierarchy.

• To add colors, expand the “Level Colors” properties and select a color.
• Do this to change the color of each node.
• All changes are automatically saved.

• Your final chart should look like the one below. If you get a license, the Sankey Chart will not have a watermark.

Insights

Our Sankey diagram shows a hierarchy that flows from Category to Subcategory to Product.

The Sales Field helps to visualize the volume or magnitude of flow between the hierarchical nodes. Level 1 shows the Store field, which helps to further filter the data.

• At level 1, Store A had the most sales at 49.62%. Store B (33.77%) was right behind it. Store C had the lowest sales at 16.61%.
• At level 2, Category, electronics were more popular than clothing (68% and 32%, respectively).
• At level 3, Subcategory, the tablets were the most popular items (19%). They were followed by computers (18%) and phones (16%). Accessories were the least-sold items.
• At level 4, iPads brought in most of the revenue (9.77%). They were followed by Android tablets (9.10%) and laptops (9.08%). Sneakers only brought in 4.46% of revenue, making them the least popular item.

What are the Benefits of Creating a Hierarchy in Power BI?

Creating a hierarchy in Power BI provides multiple benefits, including enhanced data analysis and visualization.

Here are the benefits of using hierarchies in Power BI:

Enhances the Quality of Your Visualization

Utilizing hierarchies enhances the quality of your visualizations, making them both informative and visually engaging.

They play a vital role in enhancing the clarity and structure of data within your visual representations.

Improved Data Exploration

Hierarchies allow users to either drill down for detailed data or drill up to access summarized information.

This interaction helps in gaining a deeper understanding of the data and identifying trends and patterns effectively.

Streamlined Reporting

A hierarchy simplifies the process of creating and maintaining reports. It enables the creation of a single visual for each level of detail. This leads to more concise and user-friendly reports.

Improved User Experience

Hierarchies enhance the user experience, streamlining data discovery and analysis for report consumers.

FAQs

How do I create a hierarchy from the same column in Power BI?

Creating a hierarchy from the same column in Power BI can be achieved through two distinct approaches:

The first method involves utilizing conditional columns and fill-downs. This technique utilizes conditional columns to categorize data into various levels. The “Fill Down” operation is then used to propagate these values throughout the column.

The other approach is to use a Dax formula like ”˜PATHLEVEL’ to create a hierarchy within the calculated column.

Note that the effective creation of hierarchies is well-suited for data exhibiting a natural hierarchical structure.

How do you create a hierarchy?

To create a hierarchy in Power BI:

• Identify the columns for inclusion in the hierarchy.
• Utilize DAX hierarchy functions such as PATH to generate the hierarchy.
• Extract hierarchy components using PATHITEM.
• Incorporate the hierarchy into visualizations.

This procedure facilitates the creation of a hierarchy through DAX functions. It outlines the logical order and extracts specific components for utilization in reports.

How do you manually create a hierarchy in Power BI?

To manually create a hierarchy in Power BI:

• Open the Data Pane.
• Right-click on a column.
• Select “New Hierarchy.”
• Drag additional fields for sub-levels.
• Arrange levels according to your needs.
• Utilize the hierarchy in visualizations.
• Customize formatting, sorting, or apply filters as required.

Not all visualizations treat hierarchies the same. Trying different types may be necessary to find the most effective approach for your data.

Wrap-Up

Leveraging hierarchies in Power BI proves instrumental for efficient data categorization, simplifying information into distinct classes.

This not only aids in streamlined data organization but also facilitates targeted data dissemination.

This ensures limited data access for authorized individuals, with information shared only within their organizational levels.

Creating a hierarchy in Power BI ensures limited data access for authorized individuals, with information shared only within their organizational levels.

Power BI offers two main hierarchy creation methods: manual configuration and DAX formulas, ensuring user flexibility.

Note that hierarchies are not established with any random information. Instead, your data must exhibit a structure resembling a tree or nest for hierarchies to be formed.

Mastering hierarchy creation empowers users to unlock Power BI’s full potential for thorough data analysis and reporting.