Creating Hierarchies in Power BI

by | Updated December 3, 2020 | Business Intelligence, Power Platform

One of the cool features in Power BI is that it allows you to create your own hierarchies. When a visual has a hierarchy, it enables the ability to drill down for additional relevant details. For example, you might have a visualization that shows the product hierarchy with revenue and you want to drill down on a specific product that is under-performing. By drilling down, you’re able to see the elements that make up the product and pin-point exactly what product or products are causing the decrease in revenue.

In this post we’ll cover how easy it is to create hierarchies in Power BI using the example above.

Levels that comprise a hierarchy in Power BI.
1) Before we build a hierarchy, we’ll need to know the levels that comprise the hierarchy. In our example, the levels are Category -> Subcategory -> Product -> Product Image URL.

2) After we know the hierarchy levels, we’ll use simple drag/drop techniques to create the hierarchy. Since Category is our first level in the hierarchy, let’s look for Subcategory and place it right on top of Category. Notice that there is a new item was created called “Category Hierarchy.”

Use drag and drop techniques to create hierarchies in Power BI.

3) Finally, we’ll drag and drop Product, and Product Image URL on the Category Hierarchy we just created and rename Category Hierarchy to Product Hierarchy by right-clicking Category Hierarchy and selecting “Rename.”

Renaming hierarchies in Power BI.
4) Now that our hierarchy is created, we can see it in action. In the top right-hand corner, we have a bar chart named “Sales Amount by Category” where the drill actions will take place. There are two methods to drill up/down. First, you can right-click on a bar and drill down or you can turn drill down on. By turning drill down on, we’ll be able to drill down on a level by clicking on it rather than doing a right click. My personal preference is to turn drill down on.

Two methods to drill up/down in Power BI.

5) To turn drill-down on, click on the chart and click on the down arrow in the top right-hand corner of the chart. Once enabled, the down arrow will have a black background.

6) We see that “Arts and Crafts is our lowest performer in terms of sales. To drill down on that item, simply click on it and the chart will immediately go to the next level.

Click on the lowest performer in Power BI to drill down to the next level.

7) To drill back up you can right-click any bar and select “Drill Up” and the report will go back up one level.

To drill up on level on Power BI, right-click on any bar and select "drill up."

Drilling up/down allows us to slice and dice our data to uncover patterns and anomalies. In this short demonstration, we saw how easy it is to create a hierarchy in Power BI and enable drill up/down functionality.

Related Posts

2 Comments

  1. Kendy

    Are you able to integrate Row Level Security into the Hierarchy and still drill up and down? Can you please explain?

  2. Taylor Valnes

    Hello Kendy,

    I reached out to the blog writer and he responded with the following, and he also sent an image which I’ve sent to you directly as well.
    You can still drill up and down when row level security is implemented. The easiest way to do it is to create security at the required level and implement security just like you would without a hierarchy.

    Regards,
    Taylor

Submit a Comment

Your email address will not be published. Required fields are marked *

Upcoming Events

september

01sep10:00 am10:30 amEnsuring Quality and Compliance for Batch Manufacturers in Life Sciences

01sep12:00 pm12:30 pmIs it Worth It to Move to the Cloud? A Look at Considerations for Current Agribusinesses Using Dynamics GP

01sep2:00 pm2:45 pmWhat’s New in Dynamics 365 Finance and Supply Chain

09sep11:00 am12:00 pmConfab Live with Stoneridge – Data Strategy and Reporting – Mining Decision Making Insights

15sep10:00 am11:00 amSolving the Biggest Challenges in Agribusiness Through Innovation and Technology

22sep12:00 am12:30 pmSimplifying Payroll and HR Management with ADP Workforce Now

22sep10:00 am10:30 amStreamlining Batch Manufacturing with Technology

22sep2:00 pm2:30 pmProcess Automation for Microsoft Dynamics D365 for Business Central, Finance and Operations and GP

23sep11:00 am12:00 pmConfab LIVE with Stoneridge - Riding the Wave 2 Release – Key Features Coming to Dynamics 365 this October

27sep(sep 27)9:00 am15oct(oct 15)11:00 amDynamics 365 Finance & Supply Chain Development Training - Online Workshop

29sep10:00 am11:00 amTop Five Reasons Why NOW is the Right Time to Move from Salesforce to Dynamics 365 Customer Engagement

29sep12:00 pm12:45 pmUnderstanding Job Costing and Tax Management in Business Central

29sep2:00 pm3:00 pmDigitalizing Horticulture & Agriculture - How to Sell Plants Online and Simplify Business Management

30sep12:00 pm4:00 pmSecurity and Permissions Training for D365 Business Central or Dynamics NAV

october

27sep(sep 27)9:00 am15oct(oct 15)11:00 amDynamics 365 Finance & Supply Chain Development Training - Online Workshop

06oct10:00 am10:30 amPreview of D365 Business Central Fall Release Features and Functionality

06oct12:00 pm12:30 pmInsider's Guide to New Features Available in the Fall Release of D365 Finance and Supply Chain

07oct11:00 am12:00 pmConfab LIVE with Stoneridge - Dataverse and Dynamics in Review – Let’s Get Technical

21oct11:00 am12:00 pmConfab LIVE with Stoneridge - Dataverse and Dynamics in Review – Let’s Get Functional

26oct(oct 26)9:00 am28(oct 28)5:00 pmStoneridge Connect Leadership and Community Conference

About Stoneridge
Stoneridge Software is a unique Microsoft Gold Partner, with emphasis on partner. With specialties in Microsoft Dynamics 365, Microsoft Dynamics AX, Microsoft Dynamics NAV, Microsoft Dynamics GP and Microsoft Dynamics CRM, we focus on attracting the most knowledgeable experts in the field to our team, and prioritize delivering stellar solutions with maximum impact for your business. At Stoneridge, we are deeply committed to your results. Each engagement is met with a dedicated team, ready to provide thorough, tailored, and expert service. Based in Minnesota, we intentionally “step into your shoes,” wherever you are. We focus on what you care about, and develop trusting, long-term relationships with our clients.

Subscribe To Our Blog

Sign up to get periodic updates on the latest posts.

Thank you for subscribing!

X