Multiple Bar or Column Charts

Let us assume that you want to see a comparative breakdown of dealer sales compared to wholesaler sales, but you want them split into multiple bar charts (which could just as easily be column charts), one for each car age range. This is how you can do it:

1. Insert a new Power View report, or open an uncluttered report, as you will need a certain amount of space for a multiple chart visualization.
2. Create a table using the following fields:
   1. Make (from the CarDetails hierarchy in the SalesData table)
   2. GrossMargin (from the SalesData table)
3. Convert the table to a bar chart (in the Design ribbon, select Bar Chart, then Clustered Bar).
4. Drag the Colour field from the Colours table into the VERTICAL MULTIPLES box in the Design area of the Field List.
5. Apply a filter to include only data for the year 2013 (as described for the initial chart that you can see in Figure 4-1 in the previous chapter).
6. Resize your multiple bar chart visualization, which, as it stands, is probably too small to be really effective.
7. Tweak the font sizes if you want to.

Your Power View report should look something like Figure 5-1. This figure includes the Layout section of the Field List so that you can see what it looks like for a multiple chart visualization.

You will notice that the title of the visualization is now GrossMargin By Color And Make, which draws the viewer’s attention to the fact that they are looking at the multiple bar charts as a whole, not as a separate set of unconnected analyses. Also, if you chose to resize the visualization, you will have seen that Power View will not only alter the size of the overall chart “container,” but it will also resize the individual charts inside it. However, all the charts inside the outer container will stay the same size.

Specifying Vertical and Horizontal Selections

In the previous section you saw how to visualize multiple charts to see how the color and make of a car affected the Gross Margin. Now let’s take this one step further, by adding another element of comparison. Suppose that now you want to extend the analysis by adding the car age range to the mix, in order to see if this can tell you anything about your margins and how to improve them.

To do this

1. Click inside the visualization that you made previously (GrossMargin By Color And Make).
2. Drag the field CarAgeBucket into the HORIZONTAL MULTIPLES box of the Design area of the Field List.

And that is it! Your visualization now has colors on the vertical axis on the left-hand side and the age range groups on the horizontal axis across the top. Yet each individual bar chart shows you the gross margin by make for each combination of color and car age group. It should look something like Figure 5-2.

Power View has added vertical and horizontal scroll bars to the visualization, so you can scroll through the available charts. You can also define the number of charts that are visible, and this is described in the next section.

Creating Horizontal Multiples

First, be aware that if you choose to place the field where the charts will be expanded into multiple charts into the VERTICAL MULTIPLES box, Power View will distribute the charts as best it can. If you add this field to the HORIZONTAL MULTIPLES box instead, then Power View will place all the separate charts in a single row and add a scroll bar to allow you to scroll through the set of “sub” charts that make up the complete visualization. An example of this layout (with the chart type set to column) is given in Figure 5-3.

Defining the Multiples Grid

Depending on the complexity of your individual charts and the density of the information they contain, you may prefer to specify the dimensions of the grid that contains the individual charts in a multiple chart visualization. Put simply, you can set the number of rows and columns that make up the matrix that holds the individual charts. If there are too many charts to be displayed at once, then scroll bars will be displayed to let you navigate, vertically and horizontally, through the set of available charts.

To show you how to define the number of charts that will be displayed at once in each row or column, let us assume that you have created a multiple column chart based on the following data:

• ∑ SIZE: GrossMargin
• LEGEND: ClientType
• VERTICAL MULTIPLES: Colour

This chart should look like that shown in Figure 5-4.

Now, let’s alter the layout and tell Power View to show the individual column charts in a 2×2 matrix. To do this

1. Click inside the multiple chart visualization or select it. Remember not to click on an individual column.
2. Switch to the Layout ribbon.
3. Click the Grid Height button and choose 2 from the popup.
4. Click the Grid Width button and choose 2 from the popup.

The visualization will change, and should look like Figure 5-5. As you can see, a vertical scroll bar has appeared to let you scroll down through the set of charts.

If you resize this visualization it will never display more than a 2×2 matrix of charts. You can, of course, alter the number of charts per row or column at any time by selecting a different grid height or grid width.

Multiple Line Charts

Adding a visualization that displays multiple line charts is virtually identical to displaying multiple bar or column charts. They too can show several data series. However they are particularly suited to showing how data evolves over time, and so that is what I propose to look at in this example. Anyway, now that you have seen how it is done, it might be worth clarifying the principles before creating the visualization. The process follows these steps:

1. Create the core chart that displays the data that you want repeated across multiple charts.
2. Add the elements that will separate out the charts into multiples—either horizontally, vertically, or both.
3. Set the number of charts in the grid, horizontally and vertically.
4. Resize the chart, adjust any legend, and tweak the font sizes.

To see this, let’s create a multiple line chart, showing the average SalesCost and average DirectCosts for all the months in the year. To allow us some insights, we will also compare these figures by client type—dealer and wholesaler.

1. Delete the existing multiple chart visualization, leaving an empty Power View report, filtered to display only data for the year 2013. Alternatively, create a new report.
2. Create a line chart with the following fields:
   1. ∑ VALUES: AverageSalesCosts and AverageDirectCosts
   2. AXIS: CarAgeBucket

   I won’t repeat all the instructions again, as it is definitely time for you to try on your own. At this point, you should see a chart like Figure 5-6.

   

   Figure 5-6. A simple line chart ready for multiples

3. Drag the MonthFull field from the YearHierarchy in the Date table into the VERTICAL MULTIPLES box.
4. Set the Grid Height to 4 and the Grid Width to 3 in the Layout ribbon.
5. Resize the visualization, and adjust the font sizes for the best effect.

Your visualization should now look like Figure 5-7.

You can switch between all the available bar and column types (clustered, stacked, 100% stacked) and see which type of visualization best gets your insights across to your audience.

Multiple Pie Charts

Multiple pie charts are, in their turn, very similar to multiple bar, line, or column charts.

So let’s imagine that you want to look at the cost of spare parts and see if this varies significantly depending on the age of the car; you also want to see these costs in multiple charts by car age group. Here is how it can be done:

1. Delete the existing multiple line visualization leaving an empty Power View report.
2. Create a pie chart with the following fields:
   1. ∑ SIZE: AverageSpareParts
   2. COLOR: QuarterNameAbbr (from the DateHierarchy)
3. Set the legend under the chart (select Show Legend At Bottom from the Layout button in the Design ribbon). You should see a chart like Figure 5-8.

   

   Figure 5-8. A simple pie chart before setting the vertical multiples

4. Drag the CarAgeBucket field from the SalesData table into the VERTICAL MULTIPLES box.
5. As there are virtually no spare parts sold in the 21-25 bracket, display the Filter pane, and click Chart. Expand CarAgeBucket, check (All), then uncheck 26-30. (This is to remind you about filters; you might not exclude data in such a cavalier fashion in reality).
6. Resize the visualization, and adjust the font sizes for the best effect.

Your visualization should look like Figure 5-9.

Hopefully these examples will give you ideas of how you can use the power of comparative charts—first to analyze and discover the information hidden in your data, and then to present it clearly to your audience. The type of chart that you use will depend on your data, of course, and some data sets are better suited to certain types of presentation. One thing to remember is that multiple charts are inevitably small, and so I really advise you not to overload them with data or you could end up by hiding rather than clarifying your analysis.

Drilling Down with Multiple Charts

One solution to the problem of data overload is to use drill-down with multiple charts just as you did with single charts in Chapter 4. All you have to do to add a drill-down hierarchy to a multiple chart is to add another descriptive element to fields used. Here is a short example:

1. Create the multiple pie chart example given previously.
2. Add a VehicleType field to the COLOR box in the Design area of the Field List under the existing QuarterNameAbbr field. The charts in the multiple chart will not change.
3. Double-click on any pie slice for (say) the age range 6-10 in one of the charts. You will drill down to the vehicle types for that CarAgeBucket.

Your visualization should look like Figure 5-10.

To return to the previous level of the data (the previous set of multiple charts), just click on the Drill Up icon at the top right of the set of multiple charts. The legend (assuming that you have kept one) will indicate the drill-down level that is currently displayed.

Drilling Down with Scatter Charts

Scatter charts also let you drill down into the data. For example, to add a second level of analysis to the existing chart, and to see sales and margin by color,

1. Add the Colour field (from the Colours table) to the DETAILS box, under Vehicle. The scatter chart will not alter.
2. Double-click on the data point for Jaguar XK (currently the car achieving the highest sales, and the data point that is near the top of the X axis, above all the others).

The scatter chart will drill down to show the sales and gross margin for this type of vehicle, but by color. This is shown in Figure 5-12.

To return to the root level of the data (the initial chart), all you have to do is to click on the Drill Up icon at the top right of the chart.

Scatter Charts to Display Flattened Hierarchies

Scatter charts generically are designed to show many, many data points. This makes them useful in, paradoxically, avoiding the need to drill down through a predefined hierarchy of data. Let’s see how to display multiple data sets on one level, rather than drilling down for them. For this to happen, of course, the source data must lend itself to the type of analysis that is required. Fortunately the source data has a field named Vehicle that combines the make and model of each car and that suits this kind of analysis. What you have to do is

1. Create a new Power Pivot report.
2. Add the following fields (in this order) to the Fields List VALUES box:
   1. Vehicle
   2. Colour
   3. GrossMargin
   4. RatioCostToSales
3. Convert the resulting table to a scatter chart (click Other Chart, then Scatter from the Design ribbon, with the table selected).

As you can see in Figure 5-13, every data point appears multiple times, once for every time there is a sale for a different color. Placing the mouse pointer over a data point will let you see exactly which model of vehicle and color is being represented. I have, as you have probably guessed, tweaked the size and display of the chart to show the data in its best light.

In the case of some scatter charts this technique can make the chart hard to decipher. However, if your scatter chart contains relatively few data points, this technique can be useful. What is more, Power View has the ability to highlight data by the elements that compose the legend; this is explained in Chapter 6.

Scatter Chart Multiples

Scatter charts, just like bar, column, and pie charts, allow you to display the data as multiple charts. Personally, I do not always find them easy to read, but in the interests of completeness (and because all forms of visualization do, after all, depend on the data as well as each user’s preferences and taste), here is how to display the sales and gross margin by color.

1. Select the chart that you made previously.
2. In the Design area of the Field List, drag the Colour field from the COLOR box to the VERTICAL MULTIPLES box.

The scatter chart will divide into a series of smaller charts, rather like the example shown in Figure 5-14.

You can tailor the display of the grid—the number of charts shown horizontally and laterally—by selecting the required value from the Grid Height and Grid Width buttons in the Layout toolbar, as was described earlier for pie, column, and bar charts.

Bubble Chart Data Labels and Legend

Apart from the points becoming bubbles, you will notice that a bubble chart automatically displays the data labels. If this is something you wish to remove, than all you have to do is

1. Click in the bubble chart.
2. Select None from the options available when you click on the Data Labels button in the Layout ribbon.

If you want to keep the data labels but alter their position relative to each bubble, then instead of None, you can choose one of the other data label options when you click on the Data Labels button in the Layout ribbon.

If you have chosen not to display the data labels but still need an indication of what element each bubble represents, then add a legend to a bubble chart. This is how:

1. Select the bubble chart.
2. Drag the field used for the DETAILS of the bubble chart to the COLOR box (Make, in this example).
3. Power View will add a legend to your visualization. It should look something like Figure 5-16.

If you were to add further fields to the DETAILS box (except if it is a field, which is in another box in the Field List as mentioned earlier), then your bubble chart will become a drill-down chart, like any other chart type that we have seen in this chapter. You can double-click on any bubble to drill down and click the Drill Up arrow to return to a previous level, exactly as you have done for other chart types.

Multiple Bubble Elements

Provided that your bubble chart is not already swamped with data points, you may be able to display multiple data elements simultaneously. Imagine that you want to see not only bubbles for each make but also by age range (or age bucket if you prefer) in the same visualization without needing to drill down to a second level in the chart.

This can be done by using a combination of the DETAILS and COLOR boxes in the Layout section of the Fields List. Here is how you can split the existing bubbles into multiple bubbles, while still identifying the make of each car.

1. Click inside the bubble chart that you created previously (or create it exactly as described earlier with the Make field placed in the COLOR box).
2. Add the CarAgeBucket field to the DETAILS box.
3. Click the DataLabels button in the Layout ribbon and select Center.

Your visualization will look like Figure 5-17. As you can see, each bubble has become multiple bubbles, one for each set of cars in each age range. You will see that each car make is always represented in the same color. In this case, a good way to see which age range a bubble represents is to add data labels.

If all this information clutters up your visualization, you can remove the data labels and the legend and only display the Make and CarAgeBucket when you hover the mouse pointer over a bubble.

To remove all the labels and legend

1. Click inside the chart.
2. Click on Layout to activate the Layout ribbon (unless this has already been done).
3. Select DataLabels, None.
4. Select Legend, None.

The bubble chart will look like Figure 5-18 (with a popup visible to show you how to see the details of the data).

Bubble Chart Multiples

Bubble charts can adapt well to multiple chart visualizations. I realize that this has been described earlier in this chapter, but I think that it is worthwhile to look at multiples of bubble charts as a separate topic. This will only take a few seconds, in any case. So, to display multiple bubble charts

1. Create (or revert to) the bubble chart as shown in Figure 5-15. This shows
   1. ∑ X VALUE: NetSales
   2. ∑ Y VALUE: RatioNetMargin
   3. SIZE: GrossMargin
   4. COLOR: Make
2. Add CarAgeBucket (also from the SalesData table) to the VERTICAL MULTIPLES box.
3. Select None from the Data Labels button in the Layout ribbon.
4. Adjust the grid height and the grid width if you want or need to, and possibly alter the placement of the legend. The visualization will look something like Figure 5-19.

Tiles with Charts

You can also apply tiles to any chart type (unless there is a play axis). However, this is described alongside the general use of tiles in Chapter 6.