4.5 Explore drill through - Video Tutorials & Practice Problems

<v Tutor>In this sub lesson we're gonna define</v> what drill through is. And we're gonna set up a drill through page. We'll go ahead and get our Power BI Desktop file open, that's gonna support this lesson, and wait a moment. So with our desktop open here, we're going to create a drill through filter. So essentially, what we're gonna do is, what we've got is this total sales by product name funnel chart. And what we wanna enable a user to do is go to one of these products on the chart here, hover over top of the bar, right click, and then allow them to drill through over to page that has more detailed information about that particular product. Now, this is one of the many ways inside of Power BI that, in my mind, make it unique, which allow you to set up drill paths so that you can guide people in the direction you want them to go for analytics. And start encouraging more summarization pages like we have here or overviews, and allow people to move to more detail in other areas of your actual Power BI Desktop report. So what we wanna do here first is let's go through and rename this page here. So right now we can see this is called page number one, we're gonna go through and actually rename that to overview here. So what I'm gonna do is just double click on page number one there, and go ahead and rename it. So I just double clicked in there and renamed it to overview. So the next thing we're gonna do here is we're going to go to the bottom of the page here. And we're gonna click this plus symbol 'cause we *went on a brand new page to this report. And what we're gonna do is we're gonna go ahead and rename this one to product information. So once again, I'm just gonna double click in there, and rename that one product information. So what I'm gonna do here next to set up this page is I'm actually going to get a page background put in. So I'm gonna go like I did in one of the previous sub lessons, I'm going to click the format. And to go to page background, I'm gonna click the Add Image button right here. So we'll go ahead and click that and go choose our page background. So let's go ahead and click that. And we're gonna choose background tWo here this time, so go ahead and click open. Same as before, we're gonna knock this transparency down to zero, make the image fit to fit. And now we've got a background and behind the scenes here, where we can actually frame our reporting and visualizations a little bit nicer and give it that nice corporate look and standard. So what we're gonna do next here is we are going to go ahead and build ourselves a table on this visualization. So what we'll do here is I'm just gonna hover over top of the visualizations pane right here. And let's go ahead and zoom in so we can see exactly we're gonna choose. And we're gonna choose this one here called table. So I'm just gonna go ahead and click on it. Once again, we will get a blank visualization that's gonna show up in the screen. And what I'm gonna do here is I'm just gonna drop it onto the screen like so. And we are just gonna go ahead and reposition it to take up pretty much the entire white space there that we have made available. So next, what we're gonna do is we're gonna start binding some data to this particular visual. So I'm gonna go to the fields pane here, and I'm going to pull data out of several tables here. So what we're gonna do first is start off with sales territories, and I'm gonna choose the country column. So this time, instead of dragging fields into the values, what I'm gonna do is I'm gonna start using these checkboxes. So I'm gonna go country. And we can see that we have some information showing up here on the screen now. What I wanna do next is go to our product table. And in product, I'm going to choose our subcategory, like so. Go ahead and click on that. Check on the product name as well, we're gonna notice that the product category came on, we don't want that. So we're just gonna go ahead and uncheck that box there 'cause we don't want that part of the hierarchy. The next thing we're gonna do is go to our order date table. And I want to choose my order date year like so. Alright, so now we've got our years on there. Finally, we're gonna go over to our sales table. So we're just gonna open this up, and we are gonna take a look at our order quantity. So we're gonna choose order quantity there. And let's go ahead and choose our total price as well. So we're starting to see a lot more detail being built up around this table here. So what we're gonna do here next, is enable our drill through. So what we're gonna do here to enable the drill throughs, what we're gonna do is go to our product table. 'Cause keeping in mind what we ultimately wanna do is come from the product name on our funnel chart on the overview page and come to this more detailed table of data here. So what I'm gonna do here is I'm gonna grab my product name from the product hierarchy. And it's gonna drag it down here to this drill through. So we can see where it says add drill through fields here, I'm just gonna drop the product name on there, like so. And what we'll also notice is at the very top of the screen, a little back arrow is gonna show up. And what that's gonna allow us to do is have a user go from one visual and drill through to this information here. And then be able to click that one to go back to the overview page where the navigation had started. So I'm gonna do here is I'm actually gonna highlight on that arrow, and I'm just going to move it over to the right hand side of the screen here. So just gonna move it off to the side here. That's good. So how about we go ahead and test this functionally. So let's see if we've actually were able to build what we were hoping. So what I wanna do here now is I'm gonna go over to the overview page. So let's click on the overview page there, and what I had mentioned was, we could hover over top one of these bars in the funnel chart, which are related to a product. And I now should be able to right click on that bar, and I'm gonna see a drill through option. And then you'll be presented with an option to drill through to the product information page. So what I'm gonna do is click on that. And what will happen is given that I clicked on the first bar that said mountain bike 200 black 42 inch, I'm just gonna go ahead and click on that. And I should be able to come over to the screen here and see a more detailed view in the table of that information. So what we're gonna do here next, we're gonna format this table and make it look a little bit nicer. And then put some conditional formatting on top of it as well. So the next exercise is to make this look a little bit nicer. But before we do that, let's actually show you how we can use this control to actually go ahead and move us back to the previous page. So I'm just gonna hover over it up over top of that arrow, hit the control key, and I can go back like this. So just like that, I can go from a more summarized view of information down to more detailed view information. And in my mind, this is one of the first steps in enabling people to start moving away from doing all their analysis using Excel tables only. So this is the first step in starting to show users what is possible with Power BI in terms of how you can change the data navigation experience. So let's go ahead and work on formatting this table to make it look a little nicer. So let's go through and start formatting this table. So once again, let's make sure the table has focus by clicking a blank area in here. And we're gonna see the gray border around the edges here. I'm going to go to my format. And what we wanna do here is, let's start off first by going to the column headers. So I'm gonna open this up here. And what we're gonna do first is I'm gonna make the text a little bit larger. So let's go ahead and make that 14 like so. So we've got a little nicer text size there. So next, let's go and choose a background color for our header here. So let's go and maybe choose a nice blue here. So let's go ahead and maybe choose that color right there. So that's kinda nice but now our fonts all of a sudden, don't show up that well. So let's go ahead and give ourselves a white font like that. So we got ourselves a little nicer background. So the column headers look pretty good. Let's go ahead down to our values here. And maybe we want to change its text size to 14 as well just so that those values show up a little bit nicer. Starting to look a little bit better here now. So let's go ahead and close the values card up. And we wanna go ahead and do the same thing on the totals card that we did to the header. So let's go up and we will choose the text size of 14, which is already the default. That's fantastic. So let's go to our background colors. Let's choose this blue here, once again, that we chose before, and change our font color to white. So we've got a table that is actually looking a little nicer from a formatting perspective. So what I do here too, is I'm actually just gonna start moving some of these columns over just a little bit to take up some of the space and make this look a little bit neater and cleaner here. So we can go ahead and start moving some of these over here. So let's actually actually move in this way first, so I should really be starting from this direction here. So let's go to the country. Even with the product subcategory over just a little bit here, we move the product name over to give it a little bit more space in case we have longer product names. With some of the other ones we'll take our order date year and move it over like so. And like that. Okay, perfect. So our visuals actually starting to look a little bit nicer. So what we're gonna work on next here is some conditional formatting of the values on this particular table to give us some additional information. So let's go ahead and do our conditional formatting here now. So what I'm gonna do here is close our total cards up and just ensure that the table visual still has focus, which we can see it does. And what we're gonna do here next is we're going to move down over to the format pane and let's go ahead and go to the style carters. And then go ahead and open up the style. And we're gonna change this to alternating rows. So let's go ahead and do that. Oops, now we can see that we've got our alternating row setup. That's not something we want, just wanted to go ahead and show you you can do that. And let's just go ahead and revert ourselves. What I'm gonna do this time, instead of reverting to default, is just click the Undo last action. So once again, showing you that we can go through the Undo action here on the Power BI Desktop, keeping in mind on the Power Creator, we couldn't do that. So that is good. So we'll just keep our styles the way they are set here. And what I wanna do here is let's just scroll down a little further and go to our conditional formatting card. So let's go ahead and open that up. So what we wanna do here is we wanna conditional format, based on the total sales. So let's go ahead and click that. And we're gonna go and choose total sales, we're gonna see that a few other options came available here in this formatting card. And what we wanna do here is then go down and turn the icon slider on. So we're gonna go ahead and turn this one on. So what we're gonna do here is enable icons to be used when we're actually doing our conditional formatting. And we can see here already that this is starting to get a little tight from a real estate perspective. So let's go ahead and move some of these fields over here. So let's just go ahead and do this like so. And we don't mind if these wrap around a little bit like this. So let's go and do that. And maybe we'll do the same thing on our order to years, let's just go ahead and maybe make that wrap a little bit there too. And give ourselves a little bit more room here for the total sales 'cause we're gonna be doing some formatting with it already. So this is once again, part of the tweaking and tuning that we're doing to our reports as we go. So let's dive into the conditional formatting here now. So after I went and turned the icon slider on, I can see that there is an advanced controls button right here. So I'm gonna go ahead and click on that, or a link, I should say. And what that's gonna do is bring up the conditional formatting dialog box. So let's go ahead and start formatting. So we're gonna leave our format by rules, so we could do it by field value or rules. We're gonna apply to values only, there's a few other options we have in here. We're gonna do it based on the total sales field, which is what we chose when we came in here. And next, what we'll do is go ahead and choose our icon layout. Let's actually move it to the right hand side. So just go move it to the right of the data. Let's then go ahead and leave the icon alignment to the top and the styles look just fine. So the nice thing about using icons and these colors here, is this actually helps out the accessible community by allowing people that are colorblind to go ahead and use the shapes to actually help determine what is happening here in the conditional formatting versus just the colors. So let's go ahead and start formatting this. So we're gonna go through and start formatting these values here. So I'm gonna say if the value is greater than or equal to zero and I'm gonna change this from percent to a number here. So let's go ahead and choose numbers here. I'm gonna actually hard code all of this in here. And I'm gonna say, anytime it is between zero and less than or equal to, let's just go ahead and choose 500,000 here. So let's type in 500,000, one two three. What I'm gonna do here is then choose this to be a number, we want those ones to be red. And then whenever we have something that is greater than or equal to 500,001, what we're gonna do is choose number here. And I'm gonna say less than or equal to 1,000,000, one two three, one two three. Like to go ahead and count that stuff out. We're gonna go ahead and choose number that will show up as yellow. And whenever we are greater than or equal to 1,000,001, like so we then also choose that to be a number. And what we can do here is let's go ahead and make this the maximum. So what I'm gonna do here is choose a number here, and I'm gonna get rid of that value here. And what that'll say is go to the maximum, so anything that is greater than or equal to 1,000,001 is going to get green. So I've got everything formatted the way I want. I'm gonna go ahead and click OK. And now we can see that the formatting has changed. So anything here that is in the 500,000 to a million range is given the yellow. Anything over a million is given green, anything below 500,000 is given the red diamond. So the last thing we're gonna do on this page here, is let's format this back button here. So I'm just gonna go ahead and click on it. We're gonna see the format button, format cards come up here. What we're gonna do is go down to the background card right here. And what we're gonna do is we're gonna set the transparency to 100%. So when we do that, we're gonna notice that it just matches the background of the actual visual itself. So now we actually have something that looks a little bit more neat and clean and matches the actual visuals themselves. So I'm just gonna move that a little bit here. And let's go ahead and reposition this. It's always kinda tricky to go ahead and grab onto like so. So that we have our back button right about there. Okay, perfect. So we have this visual looking exactly the way we want it. So let's go ahead and save our work here like so. Alright, so that's pretty much it. So that takes us to the end of the sub lesson here on creating drill throughs. And as I mentioned, right off the hop here, drill throughs are fantastic because they really do start enabling you to change the way people interact and consume information. Now, this is the first step in that process. But this is certainly a good step in that journey towards changing the way people view their information.