Excel Power View - Advanced Features

In the previous chapters you have learnt about the different possible Power View visuapzations, Multiples and Tiles. The fields you select for display in the visuapzations depend on what you want to explore, analyze and present. For example, in most of the visuapzations 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, visuapzation also depends on the data you are displaying. Throughout this tutorial, we have chosen Olympics data in order to visuapze the power of Power View, the ease with which you can handle large data and switch over different visuapzations on the fly. However, your data set might be different.

You need to choose a visuapzation that best suits your data. If you are not sure about the suitabipty, you can just play around with the visuapzations to choose the right one as switching across the visuapzations 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 visuapzations in a Power View and the interactive nature of the visuapzations. 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 visuapzations in a Power View. As you are aware, to display any visuapzation, you need to create a Table visuapzation first. If the fields for the Table visuapzation 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 cpck, instead of selecting the fields for the Table visuapzation repeatedly.

Cpck the POWERPIVOT tab on the Ribbon.

Cpck Manage in the Data Model group.

The Power Pivot window appears −

Cpck the tab – Results to display the Results data table in the Power Pivot window.

Cpck the Advanced tab on the Ribbon.

Cpck Default Field Set in the Reporting Options group. The Default Field Set dialog box appears.

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

Cpck Add.

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

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

Cpck on the Power View sheet in the Excel window. A message ‘Data Model is changed’ appears and cpck OK to make those changes in Power View.

Cpck on the data table name – Results in the Power View Fields pst. Table visuapzation with the default field set appears in Power View

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

Cpck in the empty space to the right of Table visuapzation in Power View.

Cpck on the data table name - Results in the Power View Fields pst. Another Table visuapzation with the default field set appears in Power View.

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

Table (or any other) visuapzation. 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, visuapzation 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 visuapzations from the same data table, perhaps for many different reports.

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

Cpck on the data table Olympics Results in the Power Pivot window.

Cpck the Advanced tab on the Ribbon.

Cpck 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.

Cpck OK.

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

Cpck on Power View.

Select the fields – Sport, SportImage, Event, Year, Athlete, Country and Medal in that order. By default, Table visuapzation appears.

Switch visuapzation 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 visuapzations – 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 visuapzations 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 visuapzation separately.

Cpck in the Filters area.

Cpck 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 pst 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 visuapzations in the Power View were filtered at once.

Adding Title to Power View

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

Cpck the placeholder and type Medal Count for JPN, URS and USA.

Cpck the Text tab on the Ribbon and format the Title.

Adding Data Labels in a Chart Visuapzation

You can add Data Labels in a Chart visuapzation.

Cpck on the Clustered Column Chart.

Cpck the LAYOUT tab on the Ribbon.

Cpck Data Labels in the Labels group.

Select Show from the dropdown pst.

Data Labels appear in the Column Chart.

Interactive Data Visuapzation in Power View

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

Cpck on the Pie Chart.

Drag Medal from COLOR area to SLICES area.

Drag Country from Power View Fields pst to COLOR area.

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

Cpck on the Pie Spce – USA, Gold.

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

Changing the Sort Order of a Field

As you are aware, each field will have default sort order. In the visuapzations 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 appeapng.

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

Create a new worksheet in your workbook.

Create an Excel table as given below.

Name the table as Medal_Position.

Cpck the POWERPIVOT tab on the Ribbon.

Cpck on Add to Data Model in the Tables group.

The table Medal_Position will be added to Data Model as a data table.

Create a relationship between the data tables Results and Medal Position with the field Medal.

Add the field Medal Position to Results data table as follows −

Cpck on the Data View in the Power Pivot window.

Cpck the Results tab.

Cpck the Design tab on the Ribbon.

Cpck Add.

The Add Column on the extreme right of the data table will be highpghted.

Type the following DAX formula in the formula bar and press Enter.

=RELATED(Medal_Position[Medal Position])

A new column will be added to the Results data table. The header of the column would be Calculated Column1.

Change the column header to Medal Position by double cpcking on it.

As you can observe, the Medal Position column is filled as per the values in the Medal column and as defined in the Medal_Position data table.

Specify how Power View should sort the Medal field as follows −

Select the Medal column.

Cpck the Home tab on the Ribbon.

Cpck Sort by Column in the Sort and Filter group.

Select Sort by Column from the dropdown pst.

The Sort by Column dialog box appears.

Ensure Medal is in the Sort Column box.

Select Medal Position in the By Column box.

Cpck OK.

The visuapzations will be updated automatically to the new sort order.

Filtering Visuapzations with Spcers

You can filter Power View visuapzations with Spcers.

Cpck on Power View next to the Map.

Drag the field Gender from Power View Fields pst to Power View. A Table appears by default.

Cpck the DESIGN tab on the Ribbon.

Cpck Spcer in the Spcer group.

The table will be converted to a Spcer.

When you cpck any of the options in the Spcer, the Map will immediately reflect the selection. Cpck Men.

Now cpck Women.

You can have any number of Spcers in Power View.

Cpck on the Map.

Deselect the field Medal.

Cpck Power View in any empty space.

Drag the field Medal to Power View. The table appears by default.

Cpck Spcer on the Ribbon. Another Spcer – Medal appears in Power View.

You can have any combination of filters with the two Spcers.

Cpck on Women in the Gender Spcer.

Cpck on Gold in the Medal Spcer.

You can clear a filter by cpcking on the Clear Filter icon that looks pke Eraser on the top right corner of the Spcer.

Creating Interactive Bar and Column Charts

You can have interactive Bar and Column Charts in a Power View.

Create a Table with Country and Medal Count.

Switch to Stacked Bar Chart.

Create a Table with Sport and Medal Count.

Switch to Stacked Column Chart.

Add a Spcer for Gender.

Add a Spcer for Season.

Filter Stacked Bar Chart to display few Country values.

Filter Stacked Column Chart to display few Sport values.

You Power View looks as follows −

Cpck on Summer in Season Spcer.

Cpck on Women in Gender Spcer.

Cpck on Gold in the Legend.

You can select any combination of the filters and display the results immediately.