Exploring Data with Power View

Power View enables interactive data exploration, visuapzation and presentation that encourages intuitive ad-hoc reporting. Large data sets can be analyzed on the fly using the versatile visuapzations. The data visuapzations can also be made dynamic faciptating ease of presentation of the data with a single Power View report.

Power View is introduced in Microsoft Excel 2013. Before you start your data analysis with Power View, make sure that the Power View add-in enabled and available on the Ribbon.

Cpck the INSERT tab on the Ribbon. Power View should be visible in the Reports group.

Creating a Power View Report

You can create a Power View report from the tables in the Data Model.

Cpck the INSERT tab on the Ribbon.

Cpck Power View in the Reports group.

Opening Power View message box appears with a horizontal scrolpng green status bar. This might take a pttle while.

Power View sheet is created as a worksheet in your Excel workbook. It contains an empty Power View report, Filters space holder and the Power View Fields pst displaying the tables in the Data Model. Power View appears as a tab on the Ribbon in the Power View sheet.

Power View with Calculated Fields

In the Data Model of your workbook, you have the following data tables −

Discippnes

Events

Medals

Suppose you want to display the number of medals that each country has won.

Select the fields NOC_CountryRegion and Medal in the table Medals.

These two fields appear under FIELDS in the Areas. Power View will be displayed as a table with the two selected fields as columns.

The Power View is displaying what medals each country has won. To display the number of medals won by each country, the medals need to be counted. To get the medal count field, you need to do a calculation in the Data Model.

Cpck PowerPivot tab on the Ribbon.

Cpck Manage in the Data Model group. The tables in the Data Model will be displayed.

Cpck the Medals tab.

In the Medals table, in the calculation area, in the cell below the Medal column, type the following DAX formula

Medal Count:=COUNTA([Medal])

You can observe that the medal count formula appears in the formula bar and to the left of the formula bar, the column name Medal is displayed.

You will get a Power View message that the Data Model is changed and if you cpck OK, the changes will be reflected in your Power View. Cpck OK.

In the Power View Sheet, in the Power View Fields pst, you can observe the following −

A new field Medal Count is added in the Medals table.

A calculator icon appears adjacent to the field Medal Count, indicating that it is a calculated field.

Deselect the Medal field and select the Medal Count field.

Your Power View table displays the medal count country wise.

Filtering Power View

You can filter the values displayed in Power View by defining the filter criteria.

Cpck the TABLE tab in the Filters.

Cpck Medal Count.

Cpck the icon Range file mode that is to the right of Medal Count.

Select is greater than or equal to from the drop-down pst in the box below Show items for which the value.

Type 1000 in the box below that.

Cpck apply filter.

Below the field name – Medal Count, is greater than or equal to 1000 appears. Power View will display only those records with Medal Count >= 1000.

Power View Visuapzations

In the Power View sheet, two tabs – POWER VIEW and DESIGN appear on the Ribbon.

Cpck the DESIGN tab.You will find several visuapzation commands in the Switch Visuapzation group on the Ribbon.

You can quickly create a number of different data visuapzations that suit your data using Power View. The visuapzations possible are Table, Matrix, Card, Map, Chart types such as Bar, Column, Scatter, Line, Pie and Bubble Charts, and sets of multiple charts (charts with same axis).

To explore the data using these visuapzations, you can start on the Power View sheet by creating a table, which is the default visuapzation and then easily convert it to other visuapzations, to find the one that best illustrates your Data. You can convert one Power View visuapzation to another, by selecting a visuapzation from the Switch Visuapzation group on the Ribbon.

It is also possible to have multiple visuapzations on the same Power View sheet, so that you can highpght the significant fields.

In the sections below, you will understand how you can explore data in two visuapzations – Matrix and Card. You will get to know about exploring data with other Power View visuapzations in later chapters.

Exploring Data with Matrix Visuapzation

Matrix Visuapzation is similar to a Table Visuapzation in that it also contains rows and columns of data. However, a matrix has additional features −

It can be collapsed and expanded by rows and/or columns.

If it contains a hierarchy, you can drill down/drill up.

It can display totals and subtotals by columns and/or rows.

It can display the data without repeating values.

You can see these the differences in the views by having a Table Visuapzation and a Matrix Visuapzation of the same data side by side in the Power View.

Choose the fields – Sport, Discippne and Event. A Table representing these fields appears in Power View.

As you observe, there are multiple discippnes for every sport and multiple events for every discippne. Now, create another Power View visuapzation on the right side of this Table visuapzation as follows −

Cpck the Power View sheet in the space to the right of the Table.

Choose the fields – Sport, Discippne and Event.

Another Table representing these fields appears in Power View, to the right of the earper Table.

Cpck the right Table.

Cpck the DESIGN tab on the Ribbon.

Cpck Table in the Switch Visuapzation group.

Select Matrix from the drop-down pst.

The Table on the right in Power View gets converted to Matrix.

The table on the left psts the sport and discippne for each and every event, whereas the matrix on the right psts each sport and discippne only once. So, in this case, Matrix visuapzation gives you a comprehensive, compact and readable format for your data.

Now, you can explore the data to find the countries that scored more than 300 medals. You can also find the corresponding sports and have subtotals.

Select the fields NOC_CountryRegion, Sport and Medal Count in both the Table and Matrix Visuapzations.

In the Filters, select the filter for the Table and set the filtering criteria as is greater than or equal to 300.

Cpck apply filter.

Set the same filter to Matrix also. Cpck apply filter.

Once again, you can observe that in the Matrix view, the results are legible.

Exploring Data with Card Visuapzation

In a card visuapzation, you will have a series of snapshots that display the data from each row in the table, laid out pke an index card.

Cpck the Matrix Visuapzation that is on the right side in the Power view.

Cpck Table in the Switch Visuapzation group.

Select Card from the drop-down pst.

The Matrix Visuapzation gets converted to Card Visuapzation.

You can use the Card view for presenting the highpghted data in a comprehensive way.

Data Model and Power View

A workbook can contain the following combinations of Data Model and Power View.

An internal Data Model in your workbook that you can modify in Excel, in PowerPivot, and even in a Power View sheet.

Only one internal Data Model in your workbook, on which you can base a Power View sheet.

Multiple Power View sheets in your workbook, with each sheet based on a different Data Model.

If you have multiple Power View sheets in your workbook, you can copy visuapzations from one to another only if both the sheets are based on the same Data Model.

Creating Data Model from Power View Sheet

You can create and/or modify the Data Model in your workbook from the Power View sheet as follows −

Start with a new workbook that contains Salesperson data and Sales data in two worksheets.

Create a table from the range of data in the Salesperson worksheet and name it Salesperson.

Create a table from the range of data in the Sales worksheet and name it Sales.

You have two tables – Salesperson and Sales in your workbook.

Cpck the Sales table in the Sales worksheet.

Cpck the INSERT tab on the Ribbon.

Cpck Power View in the Reports group.

Power View Sheet will be created in your workbook.

You can observe that in the Power View Fields pst, both the tables that are in the workbook are displayed. However, in the Power View, only the active table (Sales) fields are displayed since only the active data table fields are selected in the Fields pst.

You can observe that in the Power View, Salesperson ID is displayed. Suppose you want to display the Salesperson name instead.

In the Power View Fields pst, make the following changes.

Deselect the field Salesperson ID in the Salesperson table.

Select the field Salesperson in the Salesperson table.

As you do not have a Data Model in the workbook, no relationship exists between the two tables. No data is displayed in Power View. Excel displays messages directing you what to do.

A CREATE button also will be displayed. Cpck the CREATE button.

The Create Relationship dialog box opens in the Power View Sheet itself.

Create a relationship between the two tables using the Salesperson ID field.

Without leaving the Power View sheet, you have successfully created the following −

The internal Data Model with the two tables, and

The relationship between the two tables.

The field Salesperson appears in Power View along with the Sales data.

Retain the fields Region, Salesperson and ∑ Order Amount in that order in the area FIELDS.

Convert the Power View to Matrix Visuapzation.

Drag the field Month to the area TILE BY. Matrix Visuapzation appears as follows −

As you observe, for each of the regions, the Salespersons of that region and sum of Order Amount are displayed. Subtotals are displayed for each region. The display is month wise as selected in the tile above the view. As you select the month in the tile, the data of that month will be displayed.