Save content
Have you found this content useful? Use the button above to save it to your profile.
AIA

Working with accounts data in Excel: Power View

by
10th Mar 2014
Save content
Have you found this content useful? Use the button above to save it to your profile.

Simon Hurst delves deeper into the reporting and visualisation capabilities of the Power View add-in for Excel 2013.

So far in this Working with accounts data series, we have been looking mainly at the process of linking to data and summarising and manipulating it, using PivotTables to produce the figures required for reporting before, in the last episode, starting to look at ways of presenting the data.

The last part finished with a quick look at the Power View add-in and how it could be used to produce, among other things, a 'playable' chart that can be used to show changes over time. This time we'll work through Power View and some of its other visualisation features in more detail.

As in the previous articles, we will use the Invoices data from Microsoft’s sample database: Northwind. [Our thanks to Microsoft for permission to use it in this tutorial. If you are not able to locate a copy of the 434kb zipped you can to download a copy here, then extract Northwind.mdb].

Creating a Power View sheet

Sadly Power View is not available with all editions of Excel 2013. Like the Inquire add-in and Power Pivot, Power View is available with the Professional Plus edition of Excel 2013. According to the Microsoft store helpline Power Pivot, Inquire and Power View are now also included in the UK commercial version of Standalone Excel 2013, available for about £100.

If you do have the right edition, you should see Power View in the Reports group of the Insert ribbon. If you haven’t used Power View before then, when you click on the Power View option, you will be asked if you want to enable the Power View add-in. Alternatively, you can create a Power View Report directly from Excel's Get External Data in the same way that you create an External Data Range Table or PivotTable:

Create Power View report in Excel 2013 from external data

If your data is already in an Excel Table, then you can just select any cell within that Table and use the Power View option on the Insert ribbon tab. You can also go straight to Insert-Power View and the Power View sheet will list all the tables in your workbook.

Where tables and PivotTables are created as objects in a normal Excel worksheet, a Power View report has its own special Power View sheet with a Power View ribbon and a field list, similar to that for a PivotTable, displaying the tables and fields that are available – split between ACTIVE and ALL. Here we are looking at our Table of Invoices:

Power View layout sheet in Excel 2013

We can just select fields by ticking the adjacent check boxes as we would do for a PivotTable and the fields are automatically added to a Power View visualisation:

Fields are added automatically to a Power View visualisation

Power View adds a table summarising the data to our Power View sheet. As you can see, so far it's just like a PivotTable, but with Power View we can choose from a range of different 'Visualisations' Here we have chosen Card from the Table dropdown in the Switch Visualisation group of the Design ribbon and added the City and Country fields:

Card view option chosen from Power View Design-Table ribbon

You can drag fields directly to the Power View area. If you drop the field within an existing object it will be added to that object. If you drag it to an empty area, a new table or chart will be created. Here we have dragged ProductName to a blank area, then added the ExtendedPrice field to the resulting table, before converting to a bar chart and choosing to sort in descending order by ExtendedPrice:

Drag field options into Power View sheet to create a new view

Maps

The visualisations that we have used up to now could have been created as PivotTables or PivotCharts, but Power View has an additional Map visualisation that can use geographical fields, such as our Country or City field, to plot values on a map or series of maps. The Layout ribbon tab allows Maps to be displayed as 'road' maps or satellite photo maps:

Map presentation in Power View

Other Power View features

We can add pictures and text boxes to our data visualisations. There is also a Filters area. Fields dragged to the Filters area are available for users to select from. In this example we have typed in our title and added a picture. We have also chosen to display the Filters Area using the Power View ribbon tab. We have added the Country field to the Filters Area and chosen to display the results for Canada:

Power View filter for Canda applied to map view

You can be even more creative and overlay one chart on top of another chart. Charts are also synchronised so you can select a bar, or use Control+click to select multiple bars, and other charts in the Power View will have the same bars highlighted:

Overlay bar chart view on Power View map chart

Instead of using the Filters Area, you can drag a field into the Power View report to create a simple table and then you can use the Slicer option in the Design ribbon to convert the table into a Slicer. In a Power View Slicer you can only select with clicks and Control+clicks rather than being able to use drags and click and Shift-click as you can in a PivotTable slicer.

Convert Power View table into a Slicer

Replies (0)

Please login or register to join the discussion.

There are currently no replies, be the first to post a reply.