Microsoft® Excel® 2013: Using Power View

Power View basics

To create a Power View report, you need a data model in the Excel workbook. If you want to follow the examples in this chapter, use the data model in the “CH10-01-Power View.xlsx” file, which contains the AdventureWorks structure of products, categories, sales, and geography.

To create your first Power View report, you need to choose Power View from the INSERT tab of the ribbon. A new worksheet is created in your workbook, containing a Power View report.

The Power View editor, shown in Figure 10-1, is a white canvas where you will draw your analysis. The Power View Fields panel contains the elements of your data model.

Figure 10-1. The Power View editor is made of a white canvas (left) and the Power View Fields panel (right).

In order to start creating a report, you need to choose the values you are interested in. In the beginning, you do not need to worry about the layout; just focus on data. Imagine that you are interested in performing an analysis of sales in different territories.

You start clicking the Territory hierarchy from DimSales and then click the Sales calculated field in FactInternetSales. Your report looks like Figure 10-2.

Figure 10-2. Adding columns to the report results in a table with the required information.

Power View added the columns of the hierarchy to a table, computed the calculated field for each row, and provided a total at the bottom. You can click the title of the report and type Sales Analysis to give it a name. Now, the numbers are fine and already show some relevant insights, but they are not easy to read. Can you easily compare United States with Europe in this report? The answer is “No” because the report mixes information at different granularities. Europe and North America are territory groups, and the report shows values at the region level, without providing subtotals. Moreover, if you are interested in comparing data quickly, then a chart is definitely a better option. Since this first table looks useful anyway, it is better to add a chart to the report instead of replacing the current table. To add a chart with only SalesTerritoryGroup and sales, click an empty area of the canvas and select SalesTerritoryGroup and Sales again. At this point, your report looks like Figure 10-3.

Figure 10-3. Adding new columns when no table is selected creates a new table.

Now, you have two tables in the same report: one with detailed information and another with summary information. Since the second table has been created to make a quick comparison of data, you now want to turn it into a chart. To do this, you simply need to change the way this second table is shown. You do this by clicking the Bar Chart button on the DESIGN tab of the ribbon (see Figure 10-4).

Figure 10-4. The DESIGN tab of the Excel ribbon shows different options to format Power View elements.

Clicking it once will turn the table into a chart, which you can see in Figure 10-5.

Figure 10-5. With the chart representation, the report looks much better.

Up to now, you have not done anything that cannot be done in Excel too, even if you are already seeing the simplicity of Power View. You do not need to follow complex procedures to create a chart; just select the data that you want to show and then select the way to represent them.

One of the most interesting features of Power View is activated by clicking one of the bars of your chart. In Figure 10-6, you can see the report as it appears after you click Europe.

Figure 10-6. Clicking Europe immediately filters both the chart and the table.

The chart acts as a filter, and the filter is applied to all the tables in the same report. By clicking Europe, we are now showing sales of Europe only. If your report contains multiple charts, they will all show the effect of the filter on their data, too. A figure can help to explain this concept. For example, if you add a new chart that shows sales divided by color, the final result will be as shown in Figure 10-7.

Figure 10-7. The filter on territory group is reflected on the colors chart too.

You can see that the color chart on the bottom shows the contribution of sales in Europe to total sales all over the world. This is an interesting feature because it means that any chart can be used as a slicer right away. The auto-filter feature of bar and column charts is not working in tables. In fact, when you select a row in a table, this selection does not filter the data model. You can turn a table made up of a single column into a slicer and then using it as a filter, as you will learn in the Using a table as a slicer section, later in this chapter.

Using the Filters pane

In the previous section, you saw that many charts can be used as slicers. Power View offers another tool to apply filters to a report: the Filters pane. The Filters pane is a panel that appears on the right of the report canvas. If the Filters pane is not visible, you can activate it by clicking the small filter icon that appears on the upper-right corner of each table in your report.

In Figure 10-8, you can see a report with the Filters pane open and the small filter button on the bar chart.

Figure 10-8. The Filters pane shows different filters applied to the report.

The Filters pane is useful because it lets you apply different kinds of filters to your report. Adding a column to the Filters pane is as easy as dragging it there from the field list. You can easily filter values using the check boxes for the different values of the column, or you can switch to Advanced Filtering mode using the button highlighted in Figure 10-9.

Figure 10-9. The Advanced Filter mode lets you create complex filter expressions easily.

For example, in Figure 10-9, you can see that we created a complex filter that shows values containing “America” or that are equal to Europe. The filters created in the Filters pane can be applied to the entire report (VIEW) or to a part of it (TABLE, CHART, or MAP) by selecting the appropriate area before adding the column to the Filters pane.

Another useful feature of the Filters pane is the calculated field filter. Imagine that you want to see all the products that sold more than a certain amount of dollars (for example, $1 million). This time, the filter is not on a column in the table but on a calculated field. This kind of filter can be applied only using the Filters pane on an item; it cannot be used to filter the entire report.

You can see in Figure 10-10 that the Filters pane is open on the table, and it shows both the columns of the table and the calculated fields that are computed for each row of the table. In the example, Sales is the only calculated field.

Figure 10-10. The Filters pane lets you filter both columns and calculated fields.

In order to filter the Sales calculated field, you can either drag the slicer (which shows the minimum and maximum values of the field as boundaries) or click the Advanced Filtering button to specify a filter. Filtering of calculated fields is available only for the table, not for the view.

Decorating your report

If you need to polish and decorate your reports, you have a couple of options. You can add images in any location using the Picture button on the Power View tab of the ribbon, and you can add and format a background image to the report using the Background Image button group on the same ribbon. Finally, you can use text boxes to add notes.

For example, in Figure 10-11, you can see a report with a gray background, the company logo on the bottom, and a note near the sales of black products.

Figure 10-11. You can decorate a Power View report using many different tools.