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)
- Text boxes and images in Power View
- Filtering reports using tiles, slicers and filters
- Matrices and cards in Power View
- Charts in Power View (this blog)
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.
Charts in Power View
Power View supports a limited number of charts. You can create bar and column charts:
|Bar chart types supported||Column charts supported|
In addition, you can also create line, scatter and pie charts:
The other types of chart supported. Note that you can also animate bubble charts, as this blog shows towards the end!
If you're used to creating charts in Excel, you will be surprised (that's an understatement) by how little you can customise charts in Power View.
Creating charts in Power View
As with most things in Power View, the starting point for creating a chart is to create a table. The more columns you group by, the more complex will be the initial chart created:
Start with a table like this, and choose to create a chart (here I'm going for a clustered bar chart).
When you choose to create a chart, Excel will automatically assign the grouping columns in this order:
|Column number||This example||Becomes|
|1||Quadrant||The vertical multiples (see below)|
|3||Centre type||The legend|
|4||Total sales||The aggregated values|
As always with charts, by far the easiest way to understand this is to look at the chart produced:
This chart shows total sales by species and centre type, for each of the East, North, South and West quadrants.
You can understand what's going on (and make changes) using the fields panel:
This panel shows that you are showing total quantity by species, with the centre type as the legend and quadrants for the vertical multiples. You can drag fields between the different sections to change the look of your chart (and this is the easiest - and as far as I know, the only - way to do this). Because there is no horizontal multiple set, when the chart runs out of columns for quadrants it starts using the next row.
Horizontal and vertical multiples
You can have horizontal and/or vertical multiples. Here's an example:
Here we're displaying centre types going across, and quadrants going down.
There are four centre types, but the chart resulting from these choices doesn't initially show the fourth one:
Where has the Shopping Park centre type gone? The answer is that you need to scroll down to see it.
The easiest way to solve this is to change the grid width to 4 columns:
Changing the grid width on the LAYOUT tab of the ribbon.
By setting the grid height and grid width to 4 rows and 4 columns, you can produce an effect like this:
We can now see a chart for each quadrant and for each centre type.
This feature (multiple charts) is probably the single biggest single selling-point of Power View - it would be hard to reproduce this effect in standard Excel.
Changing the chart title
As far as I can see, there is no way to change the text of the chart title created for you:
Not keen on this title? Tough!
Because of this, I normally remove the default chart title and add a free format text box (as explained earlier in this blog) as my title, to give more control:
Choose this option on the LAYOUT tab of Excel to remove your chart title.
The chart legend
The legend for a chart appears to the right, by default, but you can change this:
The default legend position.
IMHO, the legend looks best at the bottom of a chart:
Here somebody (let's face it, me) has already set the legend to appear at the bottom of the chart, using this LAYOUT tab menu option.
Data labels have a few pre-set positions - here's an example of one of them, to give you the idea:
Here somebody (yup, me again) has already positioned data labels to appear outside the end of bars, although as you can see this isn't quite what's happened.
Playing bubble charts
I can't resist ending this blog by showing something ever so slightly gimmicky - playing bubble charts over time:
These are the total sales figures for April 2014 (you can see this as a watermark on the chart), but if you wait a second Power View will change the bubbles and watermark to the next calendar month.
To get this to work, you'll need to add in a sensible date aggregation column into your tabular model:
In the Pos table, go to the first new column, type in this formula and press the Enter key.
Now right-click to rename this newly-created column:
Right-click on this column to rename it, and call it PosMonth.
Don't worry about the fact that we haven't covered any of this yet in this tutorial. I could have played the chart day-by-day using the PosDate column, but it would have been a bit slow!
You now need to unhide the following columns and tables from client view in your model:
|Pos||PosMonth (your new column)|
|Centre||SquareMetres and NumberUnits|
Once you've deployed your model, you'll be good to go with a Power View chart (you'll need to either create a new workbook, or close and re-open the one you're using). The first thing to do is to create a table:
Choose the fields shown to create your table.
You should now have your table:
The table we're going to turn into a chart.
Make this table into a scatter chart:
Choose this menu option on the DESIGN tab of the ribbon.
You'll get better results if you show average statistics:
Click on the arrow to the right of each of the two fields NumberUnits and SquareMetres, and choose to average them in each case.
You should now have a presentable chart:
We're showing total sales for each centre type.
Now the fun bit! Add the PosMonth calculated column that you've created to the Play Axis:
Click on the arrow to the right of the PosMonth field, and choose this option.
Here's the full spec of what you've chosen:
The combination of fields for this chart.
You can now - finally - click on the play tool shown below, and watch your bubbles changing sizes month by month:
Click on the blue play triangle and sit back and watch!
Enough to bring a smile to anyone's face, and a good high point on which to end this tutorial.