Excel Power View - Overview

Power View enables interactive data exploration, visualization, and presentation that encourages intuitive ad-hoc reporting. Large data sets can be analyzed on the fly using versatile visualizations in Power View. The data visualizations are dynamic, thus facilitating ease of presentation of the data with a single Power View report.

Power View is based on the Data Model in your workbook. Either you can start with a Data Model that is already available in Power Pivot or you can create a Data Model from Power View itself. In this tutorial, we assume that you are aware of the Data Model concepts in Power Pivot. Otherwise, we suggest you to go through the Excel Power Pivot tutorial first.

Creating Power View

To create a Power View, first you need to make sure that the Power View add-in is enabled in your Excel. You can then create a Power View sheet that contains Power View, which can hold several different data visualizations based on your Data Model. You will learn how to create a Power View sheet and Power View in the chapter - Creating a Power View.

Understanding Power View Sheet

The Power View sheet has several components such as Power View canvas, Filters area, Power View Fields list, Power View Layout areas and Power View tabs on the Ribbon. You will learn about these components in the chapter – Understanding Power View Sheet.

Power View Visualizations

The core of the Power View is in its various types of data visualizations that will enable you to portray the data, visualize, and explore, all in a dynamic mode. You can handle large data sets spanning several thousands of data on the fly switching from one visualization to another, drilling up and drilling down the data displaying the essence of the data.

The different Power View visualizations that you can have are the following −

• Table
• Matrix
• Card
• Charts
  • Line Chart
  • Bar Chart
  • Column Chart
  • Scatter Chart
  • Bubble Chart
• Map

You will learn about these visualizations in different chapters in this tutorial. You will also learn about combination of visualizations on a Power View and their interactive nature.

Visualization with Multiples

Another domain of Power View is its facility to visualize the Chart visualizations in Multiples. You can have a grid of Charts in Power View with the same axis. You can have Horizontal Multiples or Vertical Multiples. You will learn Multiples in Power View with different types of charts in the chapter - Visualization with Multiples.

Visualization with Tiles

When you have large data to display at a time, browsing up and down could take time. Power View makes this task very easy for you with Tiles. Tiles are containers on a navigation strip that is based on a field in your data. Clicking on a Tile is equivalent to selecting that value of the field and your visualization is filtered accordingly. You can have data bound images such as sport images for Tiles that will give a visual cue to your navigation strip. You will learn about Tiles in the chapter – Visualization with Tiles.

Advanced Features in Power View

Whenever you have to create a visualization, you need to first create a Table and then switch visualization to the required one. This would make you create the Tables several times during data exploration and reporting. Even after you decide on what fields to portray in your visualizations, you have to repeatedly select all these fields every time. Power View provides you with an advanced feature to define a default field set that enable you to select all the fields with a single click on the table name in the Power View Fields list.

You can set table behavior, filter all the visualizations together with the VIEW tab in filters, change the sort order of a field, filter visualizations with Slicers, add data labels and add a title to the Power View. You will learn about these and more in the chapter – Advanced Features in Power View.

Power View and Data Model

We said Power View is based on the Data Model. You can either work on the Data Model that is already present, as is the case in all the previous chapters, or create one from the Power View sheet. You will learn about adding data tables to Data Model, creating relationships between tables and modifying Data Model from Power View sheet in the chapter – Power View and Data Model.

Hierarchies in Power View

If you data has nested fields you can define a hierarchy so that you can treat all the nested fields as one field. You can have a defined hierarchy in the Data Model that you can visualize from Power View or you can create a hierarchy in Power View and use it for visualization. You can drill p and drill down the hierarchy in Matrix, Bar Chart, Column Chart and Pie Chart visualizations. You can filter hierarchy in Pie Chart with Column Chart. You will learn all these in the chapter – Hierarchies in Power View.

Key Performance Indicators (KPIs) in Power View

Key Performance Indicators (KPIs) enable you to track the progress against the set goals. You can create KPIs in the Data Model from Power View. You can then create appealing Power View visualizations that depict the KPIs and produce aesthetic reports. You can also edit the KPIs from Power View as it is possible that the KPIs might have to be modified as the time progresses. You will learn about KPIs in the chapter - Key Performance Indicators (KPIs) in Power View.

The chapter also has a brief introduction on KPIs, KPI parameters and how to identify KPIs. But, note that this is not exhaustive as KPIs depend on the field you have chosen to track the progress – for e.g. business performance, sales, HR, etc

Formatting a Power View Report

The Power View visualizations as you learn throughout the tutorial are ready to present any time, as they are all appealing and presentable. Because of the dynamic nature of the visualizations, it is easy to display the required results on the sport without much effort or time. As everything is visual, there will not be any need to preview the results. Hence, in Power View reporting is not the final step and can be at any point of time with your Power View and visualizations.

In the chapter – Formatting a Power View Report, you will learn some more features that can change the look and feel of your already appealing Power View reports. These include changing Theme, setting background image, changing background colors, font and text size, formatting numbers and changing number aggregates.

Sharing Your Work

You can share your work with the concerned as the Power View sheets in your Excel workbook itself on server / cloud by giving appropriate permissions to read /edit. As Power View is also available on SharePoint, you can share your Power View reports as SharePoint reports. You can print Power View sheets, but as they are static, it would not make much sense to print them because of their innate powerful features of interactivity and dynamic nature. You can publish Power View sheets to Power BI.

Data Acknowledgements

The data that is used to the maximum extent is the Olympics results data for the years 1900 – 2014. The data table has around 35,000 rows of data that could reveal the power features of the optimistic Data Model and Power View visualizations.

Sincere thanks to all those involved in providing this data onsite −

• Olympics Official Results − https://www.olympic.org/olympic-games

• Olympics Sport Images − http://upload.wikimedia.org/wikipedia

Last but not the least Microsoft Office Support that gave the idea of choosing Olympics data to render the power of Power View Visualizations.