Creating Excel Dashboards with Power View
 |
| Microsoft Power View |
The feature that’s generated much excitement among those working in business intelligence is Power View. Only available in the Office Professional Plus version, it has the ability to turn a mass of data into meaningful graphics – ideal if you need to present complex information such as sales by location, especially as you can integrate Bing Maps. Coupled with the PowerPivot add-in, it turns Excel into a genuine business intelligence tool, giving even small businesses the means to intelligently analyse their data – and act on it.
Have you ever looked at a dashboard someone made and went, “man, I like that, but there’s too many steps for me to remember”? Or maybe wanted to have a way to play around with your data in a safe space so you don’t mess it up? Power View is a new add-in for Excel 2013 that consumes the Data Model. For those who are avid readers of our blog, you will remember Diego did a post on the Data Model we’ve integrated into Excel. If you don’t remember that post (because not all of us are perfect), the gist is that there’s a new way to have lots of data in Excel and not slow it down while also having handy things like relationships (to make your handy dandy new in-workbook cube relational). Another thing to note is that the Power View add-in comes installed by default in Professional Plus versions of Office. The point of Power View is to make it very easy to create pretty, interactive data presentations (or “reports” as the experts call them) that will make your boss go “Wow, how did you do that? You’re a genius!”
Well, OK, maybe not that far but you’ll definitely look super smart. Plus, it also can be used to explore your data visually without worrying about messing anything up and even make reports on your own, without IT help. Some of you may be quizzical and asking yourselves, “But… Doesn’t Excel already have graphs and charts and visualisations?” To which I would say, “Yes!!” and probably also add that with Power View, you get automatic cross-filtering of visualizations, new visualizations that aren’t in Excel (such as a "play" axis, maps, and cards), a free-form canvas that doesn't have the restriction of gridlines (no more messing up alignment because you inserted a new row!), unique filtering options (e.g. sliders), and support for images. Plus, if you’re a data head, you’ll be excited to know that Power View sheets in Excel can also be hooked up to external data models (i.e. Analysis Services Tabular Models). Then there are the new Apps for Office, which allow users to quickly add extra features via an online store. Apps are currently thin on the ground, but the Bing Maps tool shows what can be done, taking a list of locations and plotting them on a map for you.
 |
| Excel dashboard generated by Power View |
Where is Power View?
IMPORTANT: Power View and Power Pivot are only available in the Office Professional Plus and Office 365 Professional Plus editions, and in the standalone edition of Excel 2013.Turn on the Power View add-in from Excel Options
If the Power View icon is grayed out on the Excel Insert menu, you can still turn it on.
Go to File > Options > Add-Ins.
In the Manage box, click the drop-down arrow > COM Add-ins > Go.
Check the Power View check box > OK.
Start Power View the first time
The first time you insert a Power View sheet, Excel asks you to enable the Power View add-in.
Click Enable.
Power View requires Silverlight, so the first time, if you don’t have Silverlight:
Click Install Silverlight.
TIP: Install Silverlight from Internet Explorer rather than other browsers.
After you have followed the steps for installing Silverlight, in Excel click Reload.
Turn on Power View in Excel 2016 for Windows
Applies To: Excel 2016 The Power View button in the Reports group has been removed from the ribbon in Excel 2016 for Windows. The interactive visual experience provided by Power View is now available as part of Power BI Desktop. All the functionality of Power View is available and will continue to be supported in Excel 2016. To turn on Power View, follow the steps below.
First, you'll need to customize the ribbon, and then turn on the Power View add-in.
Customize the ribbon
On the File tab, > Options > Customize Ribbon.
File > Options > Customize ribbon
Under Main Tabs, click the tab where you want to add the new group, and then click New Group.
New Gtroup button in the Customizr Ribbon Excel options
Under Choose commands from, click Commands Not in the Ribbon, and then in the list, pick Insert a Power View Report.
Customize the ribbons box in Excel
With both Insert a Power View Report and New Group (custom) selected, click Add, and then move the New Group (custom) to where you want it on the ribbon.
Add button in the Customize Ribbon dialog in Excel
Click New Group (custom), > Rename, and then in the Display Name box, type Reports or the group name you want.
Rename button in Customize Ribbon dialog
Click OK twice.
Turn on the Power View add-in
The first time you insert a Power View sheet (click the ribbon button you added), Excel prompts you to turn on the Power View add-in.
Custom Pivot View button and dialog turning on the add-in in Excel
Click Enable.
NOTE: The new option, File > Options > Advanced > Enable Data Analysis add-ins: Power Pivot, Power View, and Power Map activates the COM Add-ins, but will not add the Power View command to the Insert tab. To see the Power View command in the ribbon, you need to follow the steps in Customize the ribbon section above.
There's a new add-in to look for errors and inconsistencies between worksheets and Power View – which used to be a Silverlight-based web tool for exploring and visualising data that you could use with SharePoint or save as PowerPoints – is now in Excel where it belongs. It's not relegated to a separate window; when you insert a Power View you get a new tab and the tools for pivoting and filtering data, plus simple layout options.
The feature that’s generated much excitement among those working in business intelligence is Power View. Only available in the Office Professional Plus version, it has the ability to turn a mass of data into meaningful graphics – ideal if you need to present complex information such as sales by location, especially as you can integrate Bing Maps. Coupled with the PowerPivot add-in, it turns Excel into a genuine business intelligence tool, giving even small businesses the means to intelligently analyse their data – and act on it.
Using Excel's Internal Data Model
Have you ever looked at a dashboard someone made and went, “man, I like that, but there’s too many steps for me to remember”? Or maybe wanted to have a way to play around with your data in a safe space so you don’t mess it up? Power View is a new add-in for Excel 2013 that consumes the Data Model. For those who are avid readers of our blog, you will remember Diego did a post on the Data Model we’ve integrated into Excel. If you don’t remember that post (because not all of us are perfect), the gist is that there’s a new way to have lots of data in Excel and not slow it down while also having handy things like relationships (to make your handy dandy new in-workbook cube relational). Another thing to note is that the Power View add-in comes installed by default in Professional Plus versions of Office. The point of Power View is to make it very easy to create pretty, interactive data presentations (or “reports” as the experts call them) that will make your boss go “Wow, how did you do that? You’re a genius!”
Well, OK, maybe not that far but you’ll definitely look super smart. Plus, it also can be used to explore your data visually without worrying about messing anything up and even make reports on your own, without IT help. Some of you may be quizzical and asking yourselves, “But… Doesn’t Excel already have graphs and charts and visualisations?” To which I would say, “Yes!!” and probably also add that with Power View, you get automatic cross-filtering of visualizations, new visualizations that aren’t in Excel (such as a "play" axis, maps, and cards), a free-form canvas that doesn't have the restriction of gridlines (no more messing up alignment because you inserted a new row!), unique filtering options (e.g. sliders), and support for images. Plus, if you’re a data head, you’ll be excited to know that Power View sheets in Excel can also be hooked up to external data models (i.e. Analysis Services Tabular Models). Then there are the new Apps for Office, which allow users to quickly add extra features via an online store. Apps are currently thin on the ground, but the Bing Maps tool shows what can be done, taking a list of locations and plotting them on a map for you.
Creating an Excel Power View Dashboard
After you have data in your internal Data Model, you can create a Power View dashboard from that Data Model. Just go to the Ribbon, click the Insert tab, and click Power View. Excel takes a moment to create a new worksheet called Power ViewX, where X represents a number that will make the sheet name unique (for example, Power View1).
This new worksheet has the three main sections shown in Figure 17-20: Canvas, Filter Pane, and Field List.
The canvas contains the charts, tables, and maps you add to your dashboard. The filter pane contains the data filters you define. You use the field list to add and configure the data for your dashboard.
The three main sections of a Power View worksheet
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 17-21) that can be checked and unchecked. 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.
Figure 17-21: The filter pane has a few icons that help you work with the filters.
To add data to the canvas, use the field list to drag the needed data fields to the FIELDS drop zone. In Figure 17-22, you can see that the Waste_Code field and the Generated_Qty field have been moved to the FIELDS drop zone. This results in a new table of data on the canvas.
Figure 17-22: 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 17-22. Again, dragging fields to the FIELDS drop zone creates these tables. After you have a data table on the canvas, you can transform it into a chart by clicking it, selecting the Design tab, and choosing a chart type. Figure 17-23 demon- strates the selection of a Clustered Bar chart.
Figure 17-23: Transform data tables in the canvas by selecting the table and choosing a chart type on the Design tab.
In Figure 17-24, note that after the data is converted to a chart, new drop zones appear in the field list. These new drop zones are used to configure to the look and utility of the chart.
Figure 17-24: When your table is transformed into a chart, new drop zones appear in the field list.
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/collapse the chart to full screen (see Figure 17-25).
Figure 17-25: Clicking a Power View chart activates a context menu for that chart.
When you select a chart in the Power View canvas, the filter pane provides a CHART option. Clicking that link allows you to see and apply custom filters to the selected chart. Figure 17-26 demonstrates filtering by the Generated_Qty field using a nifty slider.
Figure 17-26: You can use the filter pane to apply chart-specific custom filters.
You can slice your chart series by dragging a new data field into the LEGEND drop zone. In the example shown in Figure 17-27, 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 17-27: Use the LEGEND drop zone to slice your chart series.
Alternatively, you can use the VERTICAL MULTIPLES or the HORIZONTAL MULTIPLES drop zone to turn your original chart into a panel of charts. Figure 17-28 illustrates how your original chart has been replicated to show a separate chart for each data item in the On_Site_Management field.
Figure 17-28: Dragging the On_Site_Management field to the VERTICAL MULTIPLES drop zone creates a panel of charts.
Another neat trick is to add drill-down capabilities to a chart, which you do by dragging a new data field to the AXIS drop zone. Figure 17-29 shows the Gen_State field dragged to the AXIS drop zone. Initially, it will seem as though nothing happened. But in the background, Power View has layered in the newly selected field as a new category axis.
Figure 17-29: Dragging a new field to the AXIS drop zone creates a drill-down effect.v
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 this case, because you added Gen_State (generator state) to the AXIS drop zone, the chart drills down to show the breakdown by state for the data point that you double-clicked (see Figure 17-30). Note the arrow icon that allows you to drill back up.
Figure 17-30: With multiple data fields in the AXIS drop zone, you can drill into the next layer of data and then drill back up using the arrow icon.
You can create as many charts as you want to your Power View canvas. And as mentioned at the beginning of this chapter, all components in the Power View window are automatically linked so that they respond to one another. For instance, Figure 17-31 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 that’s made up by the Arkansas data — all without any extra work from you!
Figure 17-31: Charts in a Power View dashboard automatically respond to one another.
Visualising data in a Power View map
The latest buzz in the dashboarding world is location intelligence: visualising data on a map to quickly compare performance by location. Since Excel 2003, we haven’t had a good way of building map-based visualizations without convoluted workarounds. Excel 2013 changes all that with the introduction of Power View maps. To add a map to your Power View dashboard, follow these steps:v
1. Start with some location data in the Power View canvas.
Figure 17-32 illustrates some Zip Code data from your Data Model.
Figure 17-32: Add location data to your Power View canvas.
2. With your location data selected, click the Design tab.
3. Choose Map from the Switch Visualization group (see Figure 17-33).
Figure 17-33: Choose to show the data as a Map.
After a moment of gyrating, Excel generates a Bing map.
As you can see in Figure 17-34, the initial map will often be fairly useless. How Excel decides to initially handle your data is a bit of a black box and varies from data set to data set. You typically need to make some adjustments to get the view you need.
Figure 17-34: Excel generates an initial Bing map.
After you create your map, try moving your location field to the different drop zones in the field list. The drop zone you end up on will vary according to how you want to see your data. In this example (see Figure 17-35), moving the Gen_Zip field to the LOCATIONS drop zone fixes your map and creates a nice view of your data by Zip Code.
Figure 17-35: Moving the Gen_Zip field to the LOCATIONS drop zone creates a nice view by Zip Code.
You have limited control over how your map looks. With your map selected, you can go to the Layout
tab and customize the map title, legend, data labels, and map background (see Figure 17-36).
Figure 17-36: The Layout tab provides a limited set of options for customizing your Power View map. The map is fully interactive, allowing you to zoom and move around using the buttons at the
top-right corner of the map, as illustrated in Figure 17-37.v
Figure 17-37: You can interactively zoom and move around on the map.
You can use the COLOR drop zone to add an extra layer of analysis to your map. For instance,
Figure 17-38 demonstrates how adding the Waste_Code field to the COLOR drop zone differentiates each plotted location based on waste code.
www.it-ebooks.info
Figure 17-38: 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
Excel grants you limited control over how your Power View dashboard looks. On the Power View tab (see Figure 17-39), you see a Themes group. Here you can set the overall font, background, and theme for your Power View dashboard.
Figure 17-39: 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. The Bing map will not change to match your theme. Figure 17-40 illustrates a full Power View dashboard with an applied theme.
Figure 17-40: A completed Power View dashboard with an applied theme.

No comments:
Post a Comment