How to Use Hierarchies for Visualizations in Power BI

Summary

The video tutorial by Pragmatic Works guides viewers through the process of using hierarchies in Power BI to enhance report interactivity and data exploration. Hierarchies allow users to organize data into levels, making it easier to navigate and analyze. The video demonstrates how to build a hierarchy with geographical data and shows its application in various visualizations like matrices, treemaps, and maps. Key features like drill down and drill up functions are explained, highlighting their usefulness in visualizing complex data structures intuitively. The tutorial encourages users to explore hierarchies further and share their experiences.

Highlights

• Learn how to make your Power BI dashboards more interactive using hierarchies! 📈
• Easily drill down from a broad overview to detailed insights with just a few clicks. 🔍
• Emily Taylor guides you through building geographical hierarchies step by step. 🗺️
• Enhance your data exploration with drill down and drill up functionalities. 🔄
• See your data visualizations come to life with intuitive hierarchies! 🌟

Key Takeaways

• Hierarchies in Power BI make data navigation a breeze, letting you drill down into specifics with ease. 🕵️‍♂️
• You can organize your data into levels, like by state, city, then bank, making analysis structured and insightful. 📊
• Drill mode in Power BI is your best friend for exploring large datasets efficiently. 🏃‍♀️
• The visual appeal of hierarchies not only enhances reports but also improves user experience. 🎨
• Power BI's hierarchies transform static data into a dynamic exploration adventure! 🚀

Overview

In the dynamic world of Power BI, hierarchies play a pivotal role in making data visualization more insightful and interactive. Emily Taylor from Pragmatic Works takes you on a tour of this powerful feature, showing how organizing data into hierarchical layers can simplify navigation and improve analysis. From sales reports breaking down by year and then month, to geography-based hierarchies by state and city, this video encompasses all.

Building a hierarchy in Power BI starts with selecting your top level data point – perhaps the state in geographical data. Emily shows how easy it is to add levels of detail like city and bank name, creating a hierarchy that can be explored deeply in various visuals. Whether it’s matrices, treemaps, or even maps, you’ll see how hierarchies enhance your ability to delve into your data with ease.

The magic of hierarchies lies in drill modes, enabling users to zoom into complex data views without losing their place. With highlights on using interactive arrows to navigate hierarchies, this video opens up new possibilities for engaging with reports dynamically. By the end, viewers are invited to share their hierarchy adventures or join further learning opportunities at Pragmatic Works, emphasizing the community spirit of shared knowledge and growth.

Chapters

• 00:00 - 00:30: Introduction The Introduction chapter, led by Emily Taylor, a powerbi trainer at Pragmatic Works, focuses on exploring the powerful feature of hierarchies in powerbi. Powerbi is designed to be interactive, allowing users to drill down into data and gain deeper insights with a few clicks. The chapter aims to enhance this interactivity by teaching how to build hierarchies, which helps make reports more intuitive.
• 00:30 - 01:30: What is a Hierarchy in Power BI? In Power BI, a hierarchy is a structured way of organizing data into various levels, which facilitates easier navigation and analysis. A typical example is in a sales report, where the hierarchy could start with the year and progressively drill down into quarters, months, and days. This hierarchical structure helps users in analyzing data efficiently, starting from broader categories to more detailed ones.
• 01:30 - 04:00: Building a Hierarchy in Power BI Desktop The chapter titled 'Building a Hierarchy in Power BI Desktop' provides an overview of the benefits and process of creating hierarchies within Power BI. The transcript explains that hierarchies allow users to view high-level data which they can then explore in more detail by drilling down through the visuals. Hierarchies improve user experience by simplifying navigation, enhancing data exploration, and improving visual representation of the data. The chapter concludes by diving into specific examples of using hierarchies, particularly with geographical data, in sales reports.
• 04:00 - 07:30: Using Hierarchies in Visuals This chapter explores how to build and use hierarchies in Power BI visuals. The lesson starts with accessing Power BI Desktop and suggests the viewer to use the same dataset used in a previous tutorial on bank failures across the United States from 2000 to 2024. The chapter emphasizes practical application through hands-on experience with the software, inviting users to follow along using the provided dataset link.
• 07:30 - 14:00: Exploring Drill Modes and Visual Interactions The chapter titled 'Exploring Drill Modes and Visual Interactions' focuses on getting acquainted with the different phases of report development in PowerBI. The chapter suggests checking out an accompanying video for a comprehensive understanding. The main task discussed is building a hierarchy within PowerBI. On the right side of the screen, the data pane is visible without a built-in hierarchy, but with visuals that incorporate geographical data, specifically using State data to analyze counts.
• 14:00 - 14:30: Practical Example with Map Visual The chapter titled 'Practical Example with Map Visual' discusses the use of a tree map to visualize the number of failed banks across different states. Each state is represented as a segment or 'piece of the pie' within the tree map, and the size of each segment corresponds to the number of bank failures in that state. The chapter also touches on the use of a matrix for data presentation, noting that the matrix is used as a simple table without a built-in hierarchy.
• 14:30 - 16:00: Conclusion and Call to Action In the conclusion and call to action chapter, the focus is on creating a hierarchy using geography data in PowerBi. The process involves hovering over the largest piece of the hierarchy, in this case, the 'State', which represents the highest level of granularity. The instructions clarify what users might encounter if they are on an older version of PowerBi, reassuring them that it's acceptable if they don't see certain interface elements like an ellipsis.

How to Use Hierarchies for Visualizations in Power BI Transcription

• 00:00 - 00:30 welcome everyone my name is Emily Taylor and I am one of your powerbi trainers here at pragmatic works today we are going to explore a pretty powerful feature inside a powerbi hierarchies powerbi is designed to be highly interactive and clickable allowing users to drill down into your data and gain deeper insights with just a few clicks to enhance this clickability and make our reports even more intuitive we can build something called hierarchy
• 00:30 - 01:00 now you've probably heard of a hierarchy before but you're not really sure what it is inside of powerbi a hierarchy and powerbi is a way to organize your data into levels making it easier to navigate and analyze for example in a sales report you might have a hierarchy that starts with the year and then drills down into the quarters months and then possibly finally even the day this structure allows users to start with a
• 01:00 - 01:30 highlevel overview and then click through in your visuals to see more detailed information in that same sales report if you wanted to look at your data based off of geography information we've got another example here for you now why do we use hierarchies hierarchies enhance the user experience uh it simplifies navigation it improves your data exploration and quite frankly it enhances your visual so let's dive
• 01:30 - 02:00 into the powerbi desktop and build our own hierarchy and apply it to some of our visuals for ourself now that we're in the desktop if you find yourself wanting to follow along you can pause now and grab your own copy by following the link below the data in this report is going to cover failed Banks across the United States from the years 2000 to 2024 it's the same data set that we use in our powerbi beginner to pro video this video is a fantastic resource if
• 02:00 - 02:30 you're wanting to get yourself acquainted with all the phases of report development and powerbi if this pequs your interest be sure to check that video out after you finish this one now let's go ahead and build our hierarchy over here on the right side of our screen we have our data pain as you can see here I don't have a hierarchy built in but I do have some visuals that have some geographical data this visual is using State data to analyze the count of
• 02:30 - 03:00 banks that have failed based off of the states this tree map is using the same thing every state has a piece of the pie inside of the tree map the piece of the pie or the piece of the tree map is dependent upon the amount of banks that have failed and then as you can see here I have added in a matrix but without a hierarchy built in this Matrix is a just simply a a table more or less so we're
• 03:00 - 03:30 going to build a hierarchy the way to do that is to hover over the largest piece of the hierarchy that you want to include in our instance we're going to be uh using geography data so we've got our state that's going to be the highest level of granularity we're going to go ahead and hover over the word State and you will see this little ellipses pop up on the right hand side now if you're running an older version of powerbi and don't have an lip SE that's okay you can
• 03:30 - 04:00 just right click right on top of the word as well and that menu will pop up and it's just as simple as that you click on the ellipses and you see a create hierarchy option go ahead and get that clicked and now you should have a hierarchy built in right here underneath the state I'm going to go ahead and hit this drop down carrot to see what is inside of my hierarchy now it is my responsibility to go through and grab the next level of granularity and all of the levels after that so underneath the
• 04:00 - 04:30 state I want to include the city so again I'm going to hover over the word city until I see that ellipses click on the ellipses and now instead of create hierarchy I want to click on add to hierarchy the hierarchy we're adding to is the state hierarchy and then finally I have one more level of granular granularity I want to include that is the bank name I want to know all of the banks that failed inside of those cities so I'm going to hover over Bank name name click on the ellipses and again add
• 04:30 - 05:00 to hierarchy State hierarchy and just like that I've built my hierarchy I've got my broadest piece with all of the different pieces that fall under that umbrella now couple of things to know sometimes you might accidentally misplace the order right here if that happens it's fairly easy to fix you're just going to head on over to model view on the left side of your screen click on your hierarchy and just like that in the
• 05:00 - 05:30 properties pain your hierarchy is able to be click and changed all right I'm going to head back to report view One Last Thing Before I build this hierarchy into my visuals is best practice would be just to rename this hierarchy right now I've got state state hierarchy and state back to back to back just based off of the way that powerbi organizes our data and alphabetical order in the data Paine so I just like to rename my hierarchy to
• 05:30 - 06:00 whatever the category is I am using I'm going to double click right on top of that title and this time I'm going to name it geography and I'm going to call it a geography drill down because that is what a hierarchy is is it is the opportunity to drill down into our data and the word drill down can be used interchangeably with the word hierarchy now that I've got my hierarchy built I can actually go in and remove my State
• 06:00 - 06:30 information from my Matrix Visual and I'm going to go ahead and drop this hierarchy in here and I want to show want you to see how different this is now at first you might just be thinking well Emily it's a state with a list of all of the state abbreviations but now that I have this hierarchy built in we have these plus signs next to all of our States now next to Alabama I was able to click the plus sign and now I have a drop down list of every single City
• 06:30 - 07:00 inside of Alabama that has a failed bank and because I added that third level of granularity I actually could click into each of these cities and get a list of what bank failed inside of that City and if there was more than one now this being said the nice part about creating a hierarchy is any time you drop that hierarchy into a visual you also unlock some um arrows right up above your
• 07:00 - 07:30 visual so that you can drill up and down into your data without having to click on all 50 states and all of the Cities within those States so that being said I'm going to go ahead and use this drill up Arrow to scoop all of my Alabama data right back up into the city level and then again right back up into the state level now before I go on with any other visuals I want to talk about these other arrows we have up above our Matrix visual because we also have three
• 07:30 - 08:00 different down arrows that are an option this one right here is simply a button this down arrow is going to turn drill mode on this allows you to drill into things by clicking on them inside of your visual super helpful if you are looking at Maps or this tree map right here or even this bar chart we'll explore clicking into the data a little bit later these two buttons over here
• 08:00 - 08:30 are the big ones that I want to talk about this double down arrow is going to be the drill down all button so this button drills down to the very next level of the hierarchy for all of the data points simultaneously I will be honest uh for a uh Matrix visual this is my least friendly button in my opinion because it doesn't keep the drill down at the state level it just creates a running list of every single city that has had a failed
• 08:30 - 09:00 bank so as you can see we've got a list of all of the cities in alphabetical order and I even could drill down to every single bank that has failed across the us since the year 2000 I'm going to go ahead and click that drill up button because I want to talk to you about this other drill down option that we have this one right here is called the expand all down one Lev button uh this button expands all of of the data points in the
• 09:00 - 09:30 visual down to the next level of the hierarchy it's useful if you want to show the next level of all of your data point simultaneously but you also want to keep the original level visible so I'm going to go ahead and click it and show you what I mean I personally love this one for the Matrix if I'm trying to see all of the data because it is showing all of the states that I've had failed Banks and then opening up so that we can see what cities failed inside of those
• 09:30 - 10:00 States so super handy and I can click that Arrow one more time to drill down into the bank name level as well going to go ahead and drill that Arrow back up I'm going to turn off my drill mode and I'm going to move on to this visual right here we've got our account of Bank Name by state you can see I've got my state inside of my y AIS I'm going to go ahead and replace that with my drill down as well and in my y
• 10:00 - 10:30 AIS and you might be thinking again not much different than before but notice we've got these arrows up here at the top now that we've added this hierarch key I'm going to turn on drill mode and then I want to show you a really cool clickable feature by turning on this drill mode let's say I know that Georgia has 93 failed Banks but I really want to know where all those failures are happening I am able to now click on Georgia and because my drill down button
• 10:30 - 11:00 is on it is drilling down into all of these cities so I can see although Georgia does have 93 field Banks a lot of these cities only have one field bank with Atlanta really taking the cake with 10 failed banks for the state of Georgia so I think they had a pretty big hand in some of those 93 failures and I can even click down into Atlanta and get a list of every single bank that failed going to go ahead and drill up as well all the
• 11:00 - 11:30 way back to the main page and move on to this visual right over here our tree map I especially love this visual in with a hierarchy built in because a lot of times I like to see the largest banks and the area that they take up inside of my tree map but it's really fun to be able to drill down into those states by turning on drill mode also going to go ahead and get that out of here and bring
• 11:30 - 12:00 in my hierarchy to my categories again looking just the same but I'm going to turn on drill mode and I'm going to click on a different state just for fun let's drill down into Texas they have 13 failed Banks but as you can see we do have some cities that took the lead it looks like Houston had two field Banks and the remaining cities only had one I can drill back up select another state
• 12:00 - 12:30 and as you can see again we've got a couple of cities that did take the lead with the majority of them only having one field Bank per City and now just for fun I'm going to go ahead and add one last visual to this report that we can add a hierarchy to I'm going to go ahead and grab a map Visual and I'm going to bring my hierarchy into the location data and I'm going to bring that total Banks measure in to my bubble size just so that I can get bubbles that are
• 12:30 - 13:00 dependent upon the number of banks that have failed it now as you can see right here in the map we've got our top states that have failed Banks I'm going to go ahead and click on Georgia but before I do that I want to turn on this drill down mode and I want to show you how clicking on our state because we have our drill down or our hierarchy set up over here in our location data we are able to add or to see a map of
• 13:00 - 13:30 every single City that a bank has failed now I can zoom in a little bit further and see that that bubble size is still dependent on the count of failures so Atlanta that has 10 failed Banks is a little bit bigger than the remaining cities but I can zoom back out because I have my drill mode on and I have my hierarchy established I can see where all all of those banks failed so now it
• 13:30 - 14:00 has a little bit more depth rather than just knowing Georgia has a lot of those failed Banks now I will tell you that because our bank names are not necessarily tied to an address this would probably be a field you would want to remove because Georgia and uh powerbi maps in general do not know how to find those Bank names so it is going to just drop a pin randomly on the city of Atlanta so so I would want to just click the little X
• 14:00 - 14:30 right here underneath that drill down to remove the bank name from this drill down menu but just like that I am able to keep my drill mode on I can drill back up and choose another state and still have access to see all of the cities where they're Lo located within the state and the bubble size is dependent on the number of failed Banks going to go ahead and drill back up and get back to to my main report now
• 14:30 - 15:00 I want to hear from you what's the most complex hierarchy that you've ever built or would like to build in powerbi drop your thoughts in the comments below and if you have any tips or tricks for working with hierarchies share them with the community let's get a conversation going and help each other out and if you want to get any more practice with hierarchies just join me for our next powerb boot camp you can get signed up on our website at pragmatic works.com thanks for