Microsoft® Excel® 2013: Using Power View

Understanding drill-down

In previous sections of this chapter, you learned the basic tools to build a Power View report. Now it is time to learn another feature, which will liven up your reports by adding much more interactivity: the drill-down. Drill-down is the action of analyzing a chart in more detail, following a predefined path.

For example, let’s consider the map chart. You have seen that it is possible to plot circles at the country level or at the city level, and that, to plot the cities, you need to create a calculated column to uniquely identify the cities. The problem with this approach is that by plotting data at the city level, you produce many points on the world map, resulting in a scattered view of data that makes it hard to get high-level insights. You can obviously generate two reports: a high-level one and a summary, so that you can get both views. However, with Power View, you have a better option—merge the two reports into a single one and use the drill-down feature.

Using drill-down, you can prepare the report at the country level and create a navigation path that starts with the country and moves down to the state province, then to the city and, finally, to the customer address. This is the drill-down path. You start with a report like the one in Figure 10-30.

Figure 10-30. The drill-down path for a map is created in the LOCATIONS panel.

The initial chart is showing only the country names, with circles that represent the sales for the whole country. To activate drill-down, you only need to double-click one of the circles (for example, Australia). The map automatically zooms into Australia and shows the next level, which is the state province name, as shown in Figure 10-31.

Figure 10-31. Double-clicking Australia shows the next level of the drill-down path.

After you start drilling-down, you will notice that a small button with an arrow appears in the upper-right corner of the map: this is the drill-up button, which you use to go back one level at a time in the hierarchy. If you click when you are at the state province, Power View will redraw the map at the country level. You can use drill-down to reach the address level and find the exact address of your customers, if needed.

The drill-down feature is not limited to maps. It works on any type of chart. For example, you can easily create a column chart that shows the sales divided by year, and when double-clicking the year, it drills down to the month, and finally to individual days. The beauty of drill-down is that the chart is no longer a static chart; it becomes a live analysis that moves along a predefined path.