Microsoft Business Intelligence Tools for Excel Analysts (2014)

PART I: Leveraging Excel for Business Intelligence

Chapter 5: Creating Dashboards with Power View

In This Chapter

· Starting a Power View dashboard

· Creating and working with Power View charts

· Visualizing data on a Power View map

Excel 2013 introduces a feature called Power View. Power View is an interactive canvas that allows you to display charts, tables, maps, and slicers in one dashboard window. The components in the Power View window are inherently linked so that they all work together and respond to any filtering or slicing you apply while using the dashboard. Select a region in one chart, and the other components in the Power View dashboard automatically respond to show you data for only that region.

This powerful feature runs on the internal Data Model in Excel 2013. This chapter shows you how to combine the internal Data Model and Power View to create powerful interactive dashboards.

Activating the Power View Add-In

Similar to the Power Pivot functionality covered in Chapter 3, the Power View functionality is only available when you activate the Power View Add-In. It’s important to note that the Power View Add-In does not install with every edition of Office 2013. For example, if you have the Office Home Edition, you will not be able to see or activate the Power View Add-In.

As of this writing, the Power View Add-In is only available to you if you have one of the following editions of Office or Excel:

· Office 2013 Professional Plus: Available through volume licensing only

· Office 365 Pro Plus: Available with an ongoing subscription to Office365.com

· Excel 2013 Stand-alone Edition: Available for purchase through any retailer

To check whether you have the Power View Add-In, look for the Power View icon on the Insert tab of the Ribbon. If you see it, then the Power View Add-In is already activated. But if you’re not so lucky, you can activate the Power View Add-In by following these steps:

1. Choose File → Options.

2. Select the Add-Ins option on the left, select COM Add-Ins from the Manage drop-down menu, and click Go.

3. Select Power View in the list of available COM Add-Ins, and click OK.

The Power View command now appears on the Insert tab. If you don’t see it, restart Excel 2013.

You must have Silver Light installed on your machine in order to use Power View. Search for Silver Light using your favorite search engine to get the free download from Microsoft.

Creating a Power View Dashboard

In Chapters 2 and 3, you discovered how you can leverage the internal Data Model in Excel 2013 to create powerful PivotTable analyses. After you've loaded your data into the internal Data Model, you can create a Power View dashboard from that Data Model. Click the Power View button on the Insert tab. Excel takes a moment to create a new worksheet called Power ViewX, where X represents a number that makes the sheet name unique (for example, Power View1).

This new worksheet has the three main sections shown in Figure 5-1:

· Canvas: Contains the charts, tables, and maps you add to your dashboard.

· Filter pane: Contains the data filters you define.

· Field list: Add and configure the data for your dashboard.

You build up your Power View dashboard by dragging the fields from the field list to the respective sections. For example, dragging the Generator_Size field to the filter pane creates a list of filterable items (see Figure 5-2) with check boxes that can be selected or deselected. The filter pane has a few icons that help you work with the filters. These icons enable you to expand or collapse the entire filter pane, clear applied filters, call up advanced filter options, or delete the filter.

To add data to the canvas, use the field list to drag the needed data fields to the FIELDS drop zone. Figure 5-3 show the Waste_Code and Generated_Qty fields moved to the FIELDS drop zone. This results in a new table of data on the canvas.

Figure 5-1: The three main sections of a Power View worksheet.

Figure 5-2: The filter pane has a few icons that help you work with the filters.

Figure 5-3: Use the field list to drag data fields to the FIELDS drop zone, resulting in a table on the canvas.

Creating and working with Power View charts

All data in Power View starts off as a table, as shown in Figure 5-3. Again, dragging fields to the FIELDS drop zone creates these tables. After you have a data table on the canvas, you can work with it in several ways.

Transform data into a chart. Click the data table and select a chart type from the Ribbon’s Design tab. Your choices are Stacked Bar, 100% Stacked Bar, and Clustered Bar.

Figure 5-4 shows the data converted to a Clustered Bar chart, with new drop zones appearing in the field list. You use the new drop zones to configure the look and utility of the chart.

Sort, filter, or expand the chart to full screen. When you click a Power View chart, a context menu appears above the chart. With this menu, you can sort the chart series, filter the chart, and expand or collapse the chart to full screen (see Figure 5-5).

Apply custom filters. When you select a chart in the Power View canvas, the filter pane provides a CHART option. You can click that link to see and apply custom filters to the selected chart. Figure 5-6 shows a chart filtered by the Generated_Qty field using a nifty slider.

Slice your chart series. Drag a new data field into the LEGEND drop zone. In Figure 5-7, the On_Site_Management field is placed in the LEGEND drop zone; as a result, the original chart is sliced by the data items in the newly placed field.

Figure 5-4: When your table is transformed into a chart, new drop zones appear in the field list.

Figure 5-5: Clicking a Power View chart activates a context menu for that chart.

Figure 5-6: Use the filter pane to apply chart-specific custom filters.

Figure 5-7: Use the LEGEND drop zone to slice your chart series.

Convert your chart into a panel of charts. You can use the VERTICAL MULTIPLES or the HORIZONTAL MULTIPLES drop zone to turn the original chart into a panel of charts (simply drag the data field into any one of the drop zones). Figure 5-8 shows how the original chart has been replicated to show a separate chart for each data item in the On_Site_Management field.

Figure 5-8: Dragging the On_Site_Management field to the VERTICAL MULTIPLES drop zone creates a panel of charts.

Add drill-down capabilities. Drag a new data field to the AXIS drop zone. Figure 5-9 shows the Gen_State field dragged to the AXIS drop zone. Initially, it seems as though nothing has happened. But in the background, Power View has layered in the newly selected field as a new category axis.

After you add your new field to the AXIS drop zone, double-click any data point in the chart. The chart automatically drills into the next level. In Figure 5-10, Gen_State (generator state) was added to the AXIS drop zone, so the chart drills down to show the breakdown by state for the selected data point.

Click the arrow icon to drill back up.

You can add as many charts as you want to your Power View canvas. All components in the Power View window are automatically linked so that they respond to one another. For example, Figure 5-11 shows two charts on the same Power View canvas. Clicking the pie slice for Arkansas (AR) dynamically recolors the bar chart so that it highlights the portion of the bar with the Arkansas data — all without any extra work from you!

Figure 5-9: Dragging a new field to the AXIS drop zone creates a drill-down effect.

Figure 5-10: With multiple data fields in the AXIS drop zone, you can drill into the next layer of data and then drill back up clicking the arrow icon.

Figure 5-11: Charts in a Power View dashboard automatically respond to one another.

Visualizing data in a Power View map

The latest buzz in the dashboarding world is location intelligence: visualizing data on a map to quickly compare performance by location. Since Excel 2003, you haven’t had a good way of building map-based visualizations without convoluted work-arounds. Excel 2013 changes all that with the introduction of Power View maps.

To add a map to your Power View dashboard, select your location data in the Power View canvas. Location data can be zip codes, which is what Figure 5-12 shows. Click the Map button on the Design tab (you can find it in the Switch Visualization group). Excel gives you a Bing map, as shown in Figure 5-13.

Figure 5-12: Add location data to your Power View canvas.

Figure 5-13: Excel generates an initial Bing map.

The initial map is often fairly useless. How Excel decides to initially handle your data varies from dataset to dataset. As you can see in Figure 5-13, Excel gives you a warning there are too many zip codes to plot on the map. In this case, we’ll need to make some adjustments to get the view you need.

Move your location field to different drop zones in the field list. Figure 5-14 shows how moving the Gen_Zip field to the LOCATIONS drop zone fixes the map and creates a nice view of the data by zip code.

Figure 5-14: Moving the Gen_Zip field to the LOCATIONS drop zone creates a nice view by zip code.

Customize the map title, legend, data labels, and background. You have limited control over how your map looks. With your map selected, click the Layout tab and customize the map title, legend, data labels, and map background (see Figure 5-15).

Figure 5-15: The Layout tab provides a limited set of options for customizing your Power View map.

Zoom in and out and move around. The map is fully interactive. Use the buttons at the top-right corner of the map to get the view you want, as shown in Figure 5-16.

Figure 5-16: You can interactively zoom in and out and move around on the map.

Add an extra layer of analysis. Use the COLOR drop zone to add an extra layer of analysis to your map. Figure 5-17 shows how adding the Waste_Code field to the COLOR drop zone differentiates each plotted location based on waste code.

Figure 5-17: Add data fields to the COLOR drop zone to add an extra layer of analysis to your map.

Changing the look of your Power View dashboard

You have limited control over how your Power View dashboard looks. From the Themes group on the Power View tab (see Figure 5-18), you can set the overall font, text size, and background.

Figure 5-18: Changing the theme of your Power View dashboard.

The theme you choose changes the colors for your charts, backgrounds, filters, tables, and plotted map points, but the Bing map doesn’t change to match your theme. Figure 5-19 illustrates a full Power View dashboard with an applied theme.

Figure 5-19: A completed Power View dashboard with an applied theme.