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!

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:

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.

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

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.

You now need to unhide the following columns and tables from client view in your model:

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.