Excel Power View - Advanced Features

In the previous chapters you have learnt about the different possible Power View visualizations, Multiples and Tiles. The fields you select for display in the visualizations depend on what you want to explore, analyze and present. For example, in most of the visualizations that you have seen so far, we have chosen Medal to analyze Medal Count by medal type. You might want to explore, analyze and present the data gender-wise. In such a case, you need to choose the field Gender.

Further, visualization also depends on the data you are displaying. Throughout this tutorial, we have chosen Olympics data in order to visualize the power of Power View, the ease with which you can handle large data and switch over different visualizations on the fly. However, your data set might be different.

You need to choose a visualization that best suits your data. If you are not sure about the suitability, you can just play around with the visualizations to choose the right one as switching across the visualizations is quick and simple in Power View. Moreover, you can also do it in the presentation view, in order to answer any queries that can arise during a presentation.

You have also seen how you can have a combination of visualizations in a Power View and the interactive nature of the visualizations. You will learn advanced features in Power View in this chapter. These features come handy for reporting.

Creating a Default Field Set for Table

You might have to use the same field set for different visualizations in a Power View. As you are aware, to display any visualization, you need to create a Table visualization first. If the fields for the Table visualization are from the same data table, you can create a default field set for Table so that you can select the default field set with one click, instead of selecting the fields for the Table visualization repeatedly.

Click the POWERPIVOT tab on the Ribbon.
Click Manage in the Data Model group.

The Power Pivot window appears −

Click the tab – Results to display the Results data table in the Power Pivot window.
Click the Advanced tab on the Ribbon.
Click Default Field Set in the Reporting Options group. The Default Field Set dialog box appears.

Click the Fields you want to select holding down the Ctrl key in the Fields in the table box.

Click Add.

The selected fields appear in the Default fields, in order box on the right side.

Click the Move Up or Move Down buttons to order the fields in the Default fields, in order box and click OK.

Click on the Power View sheet in the Excel window. A message ‘Data Model is changed’ appears and click OK to make those changes in Power View.
Click on the data table name – Results in the Power View Fields list. Table visualization with the default field set appears in Power View

Note that you have to click only on the data table name in the Power View Fields list to select the default field set. If you click on the arrow next to data table name, it expands showing all the fields in the data table and in Power View, Table visualization does not appear.

Click in the empty space to the right of Table visualization in Power View.
Click on the data table name - Results in the Power View Fields list. Another Table visualization with the default field set appears in Power View.

As you can see, you are able to create a Table visualization with 5 fields in the desired order with a single click using the default field set. This eliminates the cumbersome selection of the 5 fields in the desired order with 5 clicks each time you want to display a

Table (or any other) visualization. However, you should be sure of which fields should be in the default field set in a data table. Hence, this feature can be used after data exploration, visualization is complete, and you are ready to produce reports. You might have to produce several reports, in which case this feature comes handy.

Setting Table Behavior

You can set the default table behavior that Power View uses to create report labels automatically for the data table. This becomes useful when you create visualizations from the same data table, perhaps for many different reports.

Suppose you have a data table – Olympics Results in the Data Model

Click on the data table Olympics Results in the Power Pivot window.
Click the Advanced tab on the Ribbon.
Click Table Behavior in the Reporting Options group. The Table Behavior dialog box appears

Select EventID under the Row Identifier box. This column should have unique values in the data table.
Check the boxes Event and EventID in the Keep Unique Rows box. These columns should have row values that are unique, and should not be aggregated when creating Power View reports.
Select Sport in the Default Label box.
Select SportImage in the Default Image box.
Click OK.

To visualize the Table behavior that you have set, select the fields as follows −

Click on Power View.
Select the fields – Sport, SportImage, Event, Year, Athlete, Country and Medal in that order. By default, Table visualization appears.

Switch visualization to Card.

The Sport field values are larger than the other field values and appear as headings for the Cards. This is because you have set Sport as the Default Label in the Table Behavior dialog box. Further, you have set SportImage as the Default Image that appears on each Card based on the Sport value.

Filtering Values in a View

Suppose you have three Power View visualizations – Matrix, Stacked Column Chart and sophisticated Pie Chart in the same Power View, each displaying different aspects of data.

You can see that all the three visualizations are displaying data for all the Country values.

Suppose you want to display data only for USA, URS and JPN. You can apply the filter criteria on the field Country in View rather than in each visualization separately.

Click in the Filters area.
Click the VIEW tab. The Filters area will be empty and no fields will be displayed, as you have not yet selected any.
Drag the field Country from Power View Fields list to Filters area. The field Country with all the values appears in the Filters area.
Check the boxes - USA, URS and JPN.

You can see that all the visualizations in the Power View were filtered at once.

Adding Title to Power View

The Title in the Power View is common to all visualizations. Hence, it should be meaningful across the visualizations. At the top of the Power View, you will see – Click here to add a title.

Click the placeholder and type Medal Count for JPN, URS and USA.
Click the Text tab on the Ribbon and format the Title.

Adding Data Labels in a Chart Visualization

You can add Data Labels in a Chart visualization.

Click on the Clustered Column Chart.
Click the LAYOUT tab on the Ribbon.
Click Data Labels in the Labels group.
Select Show from the dropdown list.

Data Labels appear in the Column Chart.

Interactive Data Visualization in Power View

The efficiency of Power View is in its ability to make you visualize data interactively within no time.

Click on the Pie Chart.
Drag Medal from COLOR area to SLICES area.
Drag Country from Power View Fields list to COLOR area.

The Pie Chart shows Country values – JPN, URS and USA as you have applied this filter to VIEW.

Click on the Pie Slice – USA, Gold.

The Matrix is filtered to show only the values corresponding to the highlighted Pie Slice. In the Column Chart, the distribution of Gold Medals among Men and Women is highlighted for USA. Thus, efficient presentations with Power View is just a click away.

Changing the Sort Order of a Field

As you are aware, each field will have default sort order. In the visualizations that you have seen so far, the Medal field is sorted by the default order – Bronze, Gold, and Silver. This is because the text field is sorted in ascending order. However, while reporting, you might want to display the order as Gold, Silver and Bronze as it would be more appealing.

Add a field by which you can sort the Medal field in the desired order as follows −