Power View is one of the latest products in Microsoft’s Power BI Suite. It’s a pretty cool tool for creating interactive reports for data insights in Excel. If you want to an overview of what Power View is and how to get started, go check out this excellent blog post. If you’re ready for some more advanced tips, read on!
Over the past few months I’ve been learning and implementing Power View solutions and I’d like to share a few tips and tricks about using drill downs. Drill downs do just what their name implies: they are actions that let you “drill” into the data to get to more granular or specific data and details. In Power View, drill downs let you save space in your views as well as add more interactivity to your data.
Let’s start with a sample of the dummy data I will be using to demonstrate drill downs:
As you can see in how this table is structured, we have a hierarchy of levels to measure dates. The most granular (or specific) level we have is Week, we can go up a level to Month, and at the broadest level we have Year.
Selecting the data table I clicked Insert -> Power View to start building my drilldown graphs. When you first open the Power View you’ll see the large blank view space and the fields section:
Drag the value field onto the view workspace. Then, change the visualization type to clustered column chart. You will notice that the field section has changed to allow more visualization areas:
In the Axis area you will want to add Year, Month, and Week in that order. It should look like this:
This is what my view looks like now:
As you can see, we’re currently looking at Value by Year. If you want to drill down to the next level, simply double click on the bar. You’ll drill down to Month which looks like this:
You can even double click further to drill down to Week:
If you want to drill up, just click the little up-arrow in the top right corner of the chart, and you’ll go up one level in the hierarchy.
One thing to note: not every visualization type supports drill downs, such as the basic table. The rule of thumb is if you can add multiple fields to the axis area, then you can create a drill down.
If your data is structured properly, then you can create some really cool interactive graphs with drilldowns in Power View. Using Power View like this is an awesome way to get your users to explore the data and gain insights. Happy reporting!
Posted by Brian Pohl