BLOGS BY TOPIC
BLOGS BY AUTHOR
BLOGS BY YEAR
Power View is an add-in included within Excel 2013 and later versions. This tutorial shows how to create Power View reports based on Analysis Services tabular models.
- Overview - what is Power View?
- Creating your first Power View report
- Creating and formatting basic visualisations (tables) (this blog)
- Text boxes and images in Power View
- Filtering reports using tiles, slicers and filters
- Matrices and cards in Power View
- Charts in Power View
Posted by Andy Brown on 08 December 2015
You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.
Creating and formatting basic visualisations (tables)
It's easy to do things in Power View, although you may (I do) get frustrated by its formatting limitations.
I'm a big fan of PowerPivot and SSAS Tabular, but Power View always seems to me as if it hasn't quite been finished! See if you agree ...
Creating a table visualisation
The generic word for a table, matrix, chart or card in Power View is a visualisation. You can create a simple table visualisation just by selecting columns:
Select the columns from the list of fields in your model. Power View will aggregate the measures chosen.
If you can't see the field list shown above, you may need to select this button:
Click on this icon on the Power View tab of the Excel ribbon to display your model's field list.
Power View will show which fields you've chosen at the bottom right:
Here we've chosen 3 fields: 2 aggregator columns and a measure.
Power View creates a table automatically for you:
The table created for this combination of columns.
The rules are: if you have an existing visualisation (such as a table) selected, Power View will add any fields you tick to it; if you don't have a visualisation selected, Power View will create a new table when you tick a field.
Selecting, moving and deleting visualisations
To move any visualisation, position the mouse pointer on any edge of it and wait for this hand symbol to appear, then click and drag:
Here we've succeeded in getting the hand symbol visible, and could now click and drag to move the table.
To delete a table, first click in it to select it:
This table is selected, believe it or not!
You can then just press the Delete key to delete the table.
A note on undoing things
Power View has excellent support for the undo feature, but be careful not to try to use the Excel quick access toolbar to undo:
The undo tool on the Power View ribbon works, but the smaller one in the quick access ribbon doesn't. As always, the easiest way to undo anything, however, is to press CTRL + Z.
Sorting tables and other visualisations
Click on any column heading to sort by that column:
Click on a column heading to sort by that column; click a second time to reverse the sort order.
Formatting tables and other visualisations
You can't add or edit borders in Power View.
To change the format of (for example) a column, first select it:
Here I'm going to display commas when the total quantity sold for a region is 1,000 or more.
You can now change the format of the thing you have selected
Click on the tool shown to display commas, then reduce the number of decimal places using the tool to its right.
You're probably now wondering where all of the other formatting tools are. The answer is that they don't exist - that's it!
Re-ordering or deleting columns (or making any other changes)
For virtually all other changes to visualisations, the secret is to use the FIELDS at the bottom of the field list:
Click and drag a field to change where it appears.
To delete a field from a table or other visualisation, choose this option:
Click on the drop arrow to the right of any field and choose the option shown to remove it.
Popping out and in
One of the oddest options in Power View allows you to make a single visualisation temporarily fill your entire report, so that you can edit it easily:
Click on a visualisation to select it, then click on this icon at the top right to "pop it out".
When you've finished making your changes, you can tell the table to resume its normal size by "popping it back in":
Click on this tool to pop a visualisation back to where it was!
And on that high note, we've finished looking at basic tables. Time to take a brief digression and look at text boxes and images in Power View.