It is one thing to have a game-changing insight that can fundamentally alter the way your business works. It is quite another to be able to convince your colleagues of your vision. So what better way to show them—intuitively and instantaneously—that you are right than with a chart that makes your point irrefutably?
Power View is predicated on the concept that a picture is worth many thousands of words. Its charting tools let you create clear and convincing visualizations that tell your audience far more than a profusion of figures ever could. This chapter, therefore, will show you how simple it can be not just to make your data explain your analysis, but to make it seem to leap off the screen. You will see over the next few pages how a powerful chart can persuade your peers and bosses that your ideas and insights are the ones to follow.
A little more prosaically, Power View lets you make a suitable data set into
- Pie charts
- Bar charts
- Column charts
- Line charts
- Scatter charts
- Bubble charts
- Multiple charts
In this chapter we will get up and running by looking at creating pie, bar, column, and line charts. The other chart types will be discussed in the next chapter. Once you have decided upon the most appropriate chart type, you can then enhance your visualization with titles, data labels, and legends, where appropriate. We will also see how to apply drill-down techniques to charts and how to filter the data that underlies them.
The sample file for this chapter is CarSales.xlsx, which you should find in the directory C:HighImpactDataVisualizationWithPowerBI—assuming you have installed the samples as described in Appendix A.
A First Chart
As with so much in Power View, it is easier to appreciate its simplicity and power by doing rather than talking. So I suggest leaping straight into creating a first chart straightaway. In this section we will look only at “starter” charts that all share a common thread—they are based on a single column of data values and a single column of descriptive elements. This data will be
- A list of clients
- Car sales for a given year
So, let’s get charting!
Any Power View chart begins as a data set. So, let me introduce you to the world of charts; this is how to begin:
- Create a new Power View report by clicking Power View in the Insert ribbon.
- Display the Field List by clicking the Field List button in the Power View ribbon (unless the Field List is already visible).
- Drag the field ClientName from the Clients table onto the Power View report canvas.
- Drag the field SalePrice from the SalesData table onto the table that was created in the report canvas during the previous step.
- Expand the YearHierarchy in the Date table and add the Year field to the Filters Area. Select the year 2013 (I’ll let you refer back to Chapter 3 if you need reminding how this is done). The data table should look something like Figure 4-1.
Figure 4-1. A source data table for charting
- Leaving the table selected, click Bar Chart, then Clustered Bar in the Design ribbon. Your chart should look like Figure 4-2.
Figure 4-2. Your first chart
- Resize the chart—I suggest widening it—by dragging the handle in the middle of the right edge to the right until the axis labels are clearly visible, as shown in Figure 4-3.
Figure 4-3. A basic chart after resizing
And that is all that there is to creating a simple starter chart. This process might only take a few seconds, and once it is complete, it is ready to show to your audience, or be remodeled to suit your requirements.
Nonetheless, a few comments are necessary to clarify the basics of chart creation in Power View:
- First, when creating the table on which a chart is based, you can use any of the techniques described in Chapter 2 to create a table. You can drag fields into the FIELDS box of the Design section of the Field List rather than onto the Power View canvas if you prefer.
- Second, when you transform a table into a chart, the Layout section of the Field List changes to reflect the options available when creating or modifying a chart. If you select the chart that you just created, you will see that the ClientName field has been placed in the AXIS box, and the SalePrice field has been placed in the VALUES box. Neither of these boxes existed when the visualization was a table. This can be seen in Figure 4-4.
Figure 4-4. The layout section of the fields list for a clustered bar chart
- Third, when using only a single data set, you can choose either clustered or stacked as the chart type for a bar or column chart; the result will be the same in either case. As you will see as we progress, this will not be the case for multiple data sets.
- Fourth, Power View will add a title at the top left of the chart explaining what data the chart is based on. You can see an example of this in Figure 4-3.
- Fifth, you can disregard the totals in the initial table. These are not used in a chart.
- Finally, creating a chart is very much a first step. You can do so much to enhance a chart and accentuate the insights that it can bring. However, all of this will follow further on in this chapter and in the next one.
Deleting a chart is as simple as deleting a table. All you have to do is
- Click inside the chart.
- Press the Delete key.
If you remove all the fields from the Layout section of the Field List (with the chart selected), then you will also delete the chart.
So you have an initial chart. Suppose, however, that you want to change the actual data on which the chart is based. Well, all you have to do to change both the axis elements, the client names, and the values represented, is
- Click on, or inside, the chart that you created previously. Avoid clicking on any of the bars in the chart for the moment.
- In the Field List, click on the popup menu for SalePrice in the VALUES box, and select Remove Field. The bars will disappear from the chart.
- Drag the field GrossMargin from the SalesData table into the VALUES box.
- In the Field List, click on the popup menu for ClientName in the AXIS box, and select Remove Field. The client names will disappear from the chart and a single bar will appear.
- Click on the popup menu for the Colour field in the Colours table and select Add As Axis (or drag the Coluor field from the Colours table into the AXIS box). The list of colors will replace the list of clients on the axis, and a series of bars will replace the single bar. Look at Figure 4-5 to see the difference.
Figure 4-5. A simple bar chart with the corresponding layout section
That is it. You have changed the chart completely without rebuilding it. Power View has updated the data in the chart and the chart title to reflect your changes.
Basic Chart Types
When dealing with a single set of values, you will probably be using the following four core chart types:
- Bar chart
- Column chart
- Line chart
- Pie chart
Let’s see how we can try out these types of chart using the current data set—the colors and Gross Margin that you applied previously.
A column chart is, to all intents and purposes, a bar chart where the bars are vertical rather than horizontal. So, to switch your bar chart to a column chart
- Click on, or inside, the bar chart that you created previously. Avoid clicking on any of the bars in the chart for the moment.
- Click Column Chart, then Clustered Column, in the Design ribbon. Your chart should look like Figure 4-6.
Figure 4-6. An elementary column chart
A line chart displays the data as a set of points joined by a line. To switch your column chart to a line chart
- Click on, or inside, the bar chart that you created previously. Avoid clicking on any of the bars in the chart for the moment.
- Click Other Charts, then Line, in the Design ribbon. Your chart should look like Figure 4-7.
Figure 4-7. A simple line chart
Pie charts can be superb at displaying a limited set of data for a single series—like we have in this example. To switch the visualization to a pie chart
- Click on, or inside, the line chart that you created previously. Avoid clicking on the line in the chart for the moment.
- Click Other Charts, then Pie, in the Design ribbon. Your chart should look like Figure 4-8. You will notice that the Layout section has changed slightly for a pie chart, and the AXIS box has been replaced by a COLOR box.
Figure 4-8. A basic pie chart
A pie chart will be distorted if it includes negative values at the same time as it contains positive values. What Power View will do is display the negative values as if they were positive, because otherwise the values cannot be displayed. This is probably not the effect that you were hoping for. If your data set contains a mix of positive and negative data, then Power View will display an alert above the chart warning you that the Pie Chart Contains Positive And Negative Values. You can see which pie slices contain negative values by hovering the mouse pointer over each slice and reading the values in the popup that appears.
In practice, you may prefer not to use pie charts when your data contains negative values, or you may want to separate out the positive and negative values into two data sets and display two charts, as explained in the “Chart Filters” section later in this chapter.
Note Juggling chart size and font size to fit in all the elements and axis and/or legend labels can be tricky. One useful trick is to prepare “abbreviated” data fields in the source data, as has been done in the case of the QuarterAbbr field in the Date table that contains Q1, Q2, and so on, rather than Quarter 1, Quarter 2, and so on to save space in the chart. Techniques for this sort of data preparation are given in Chapter 10.
Essential Chart Adjustments
Creating a chart in Power View is, I hope you will agree, extremely simple. Yet the process of producing a telling visualization does not stop when you take a table of data and switch it into a chart. At the very least, you will want to make the following tweaks to your new chart:
- Resize the chart.
- Reposition the chart.
- Sort the elements in the chart.
- Alter the size of the fonts in the chart.
None of these tasks is at all difficult. Indeed it can take only a few seconds to transform your initial chart into a compelling visual argument—when you know the techniques to apply.
A chart is like any other visualization on the Power View report and can be resized to suit your requirements. To resize a chart
- Place the mouse pointer over any of the eight handles that appear at the corners and in the middle of the edges of the chart that you wish to adjust. The pointer becomes a two-headed arrow.
- Drag the mouse pointer. As you are resizing the chart, its background changes color to indicate that it is selected.
Note You do not have to select or click inside a chart before you resize it. Remember that the lateral handles will let you resize the chart only horizontally or vertically, and that the corner handles allow you to resize both horizontally and vertically.
When resizing a chart, you will see that this can have a dramatic effect on the text that appears on an axis. Power View will always try and keep the space available for the text on an axis proportionate to the size of the whole chart.
For bar charts, this can mean that the text can be
- Adjusted to spread over two or more lines
- Cut, with words split over two rows
- Truncated, with an ellipsis (three dots) indicating that not all the text is visible.
For column and line charts, this can mean that the text can be
- Adjusted to spread over two or more lines
- Angled at 30, 60, or 90 degrees
- Truncated, with an ellipsis (three dots) indicating that not all the text is visible
If you reduce the height (for a bar chart) or the width (for a column or a line chart) below a certain threshold, Power View will stop trying to show all the elements on the non-numeric axis. Instead it will only show a few elements and will add a scroll bar to allow you to scroll through the remaining data. You can see an example of this for a bar chart in Figure 4-9.
Figure 4-9. A chart with a scroll bar visible
All this means is that you might have to tweak the size and height to width ratio of your chart until you get the best result. If you are in a hurry to get this right, I advise using the handle in the bottom right corner to resize a chart, as dragging this up, down, left, and right this will quickly show you the available display options.
You can move a chart anywhere inside the Power View report:
- Place the mouse pointer over the border of the chart. The pointer changes into a hand. As you are repositioning the chart, its background changes color to indicate that it is selected.
- Drag the mouse pointer.
Sometimes you can really make a point about data by changing the order in which you have it appear in a chart. Up until this point you have probably noticed that when you create a chart, the elements on the axis (and this is true for a bar chart, column chart, line chart, or pie chart) are in alphabetical order by default. If you want to confirm this, then just take a look at Figures 4-5 to 4-8 on the preceding pages.
Suppose now, for instance, you want to show the way that sales are affected by the color of the vehicle. In this case, you want to sort the data in a chart from highest to lowest so that you can see the way in which the figures fall, or rise, in a clear order. Here is how to do this:
- Select the Clustered Bar chart type, as described earlier (and shown in Figure 4-5).
- Place the mouse pointer over the chart. You will see that Sort By Color Asc appears over the chart on the top left. This is shown in Figure 4-10.
Figure 4-10. The sort area in a chart
- Click on the word Colour. This will change to GrossMargin, and the sort order of the elements in the chart will change.
- Let’s suppose now that you want to see the sales by color in descending order. Place the mouse pointer over the chart. You will see that Sort By GrossMargin Asc appears over the chart on the top left.
- Click Asc. This becomes Desc, and the chart changes to become like it is in Figure 4-11.
Figure 4-11. Sorting data in a bar chart
If a chart has multiple values, as will be the case for some of the charts that you will see further on in this chapter, then you have two options when selecting the field on which the chart will be ordered:
- Click on the field name that appears above the top left of the chart when the mouse pointer is placed over the chart (as we did a moment ago). Each click will change the sort to the next available field used by the chart and then continue to cycle through the fields.
- Click on the downward-facing triangle to the right of the currently selected sort field to get a popup list of available fields to sort on. Then click on the field you want to use as a basis for the sort. This is shown in Figure 4-12.
Figure 4-12. Selecting the sort element in a chart
I should add just a short remark about sorting pie charts. When you sort a pie chart, the pie chart will be sorted clockwise, starting at the top of the chart. So if you are sorting colors by GrossMargin in descending order, the top selling color will be at the top of the pie chart (at 12 o’clock), with the second bestselling color to its immediate right (2 o’clock, for example) and so on. An example of this is shown in Figure 4-13.
Figure 4-13. Sorting data in a pie chart
When a chart is initially created, Power View will apply a default font size. This font size will not change proportionally if you resize the chart. This does not, however, mean that Power View fixes font sizes definitively. You can influence matters by choosing proportionally to reduce, or increase, the size of the fonts used on both axes of the chart. You can do this in the following way:
- Select the chart (but do not click on any of the bars, columns, or lines).
- In the Design ribbon, click on the Increase Font Size button to make the fonts in the chart larger, or on the Decrease Font Size button to make the fonts in the chart smaller.
Adjusting the font size will produce many of the same effects that you saw when you were resizing a chart; namely, text on the axes will be
- Adjusted to spread over two or more lines
- Truncated, with an ellipsis (three dots) indicating that not all the text is visible
- Angled at 30, 60, or 90 degrees
If Power View considers the text too large to display all the elements in the chart, then it will only show a few elements and will add a scroll bar to allow you to scroll through the remaining data.
Applying Color to Bar and Column Charts
The bar and column charts that we have created so far are a little lacking in color, as every bar or column is the same shade. If you want to add a splash of color, then you can override the default and make Power View apply a palette of colors to the bars and columns for a chart based on a single data element.
For example, take the column chart shown in Figure 4-5; all you have to do is
- Drag the Colour field from the AXIS box into the LEGEND box in the Layout section of the Field List.
You can see the result in Figure 4-14. The axis titles are now the legend elements, and the bars are in different colors. Admittedly, on the printed page you cannot see the colors, but the shading indicates that your changes have worked! You can get a similar result for a bar chart.
Figure 4-14. Applying a color palette to individual columns
Note You cannot sort a chart like this one—one that does not have any axis values.
Multiple Data Values in Charts
So far in this chapter we have seen simple charts that display a single value. Life is, unfortunately, rarely that simple, and so it is time to move on to slightly more complex, but possibly more realistic, scenarios where you need to compare and contrast multiple data elements.
For this set of examples, I will presume that we need to take an in-depth look at the indirect cost elements of our car sales to date. These are
- Delivery
- Parts
- Labor
All of these can be found in the CarSalesData table.
Consequently, in order to begin with a fairly simple comparison of these indirect costs, let’s start with a clustered column chart:
- Starting with a clean Power View report, create a table that displays the following fields:
- ClientName (from the Clients table)
- SpareParts (from the SalesData table)
- DeliveryCharge (from the SalesData table)
- LabourCost (from the SalesData table)
- Filter to include only data for the year 2013 as described for the initial chart that you created at the start of this chapter.
- Leaving the table selected, click Bar Chart, then Clustered Bar, in the Design ribbon.
- Resize the chart to make it clear and comprehensible, as shown in Figure 4-15 (I have included the Fields List so that you can see this too).
Figure 4-15. Multiple data values in charts—a clustered bar chart with the layout section shown
You will notice that a chart with multiple data sets has a legend by default, and that the automatic chart title now says SpareParts, DeliveryCharge, And LaborCost By ClientName.
The same data set can be used as a basis for other charts that can effectively display multiple data values. These are
- Stacked bar
- Clustered column and stacked column
- Line charts
As column charts are essentially bar charts pivoted through 90 degrees, I will not show examples of these here. However, in Figures 4-16 and 4-17, you will see examples of a stacked bar chart and a line chart. You will also see that when creating these types of visualization, the Layout section of the Field List remains the same for all of these charts.
Figure 4-16. A simple stacked bar chart
Figure 4-17. An introductory line chart
Note You cannot create a stacked bar or stacked column chart directly from a table that has multiple numeric data values; so you have two choices: either you start with a table containing only one numeric data value and then drag the other numeric fields that you wish to use onto the stacked chart (or into the  VALUES box), or you start with a clustered chart that you then convert into a stacked chart.
To conclude our tour of basic charts, I just want to make a couple of comments.
First, you can always see exactly what the figures behind a bar, column, line, point, or pie segment are just by hovering the mouse pointer over the bar (or column, or line, or pie segment). This will work whether the chart is its normal size, or whether it has been popped out to cover the Power View report area. An example of this is given in Figure 4-18.
Figure 4-18. A clustered bar chart with the popup displayed
Second, however much work you have done to a chart, you can always switch it back to a table if you want. Simply select the chart, and select the required table type from the Table button in the Design ribbon. If you do this, you will see that the table attempts to mimic the design tweaks that you applied to the chart, keeping the font sizes the same as in the chart, and the size of the table identical to that of the chart. Should you subsequently switch back to the chart, then you should find virtually all of the design choices that you applied are still present—unless, of course, you made any changes to the table before switching back to the chart visualization.
You have already seen most of the basic charting techniques. I hope that you found them as simple as I promised they would be. So, before we move on to the next level, I really should explain a new Power View ribbon that appears every time you click on a chart. It is the Layout ribbon.
The Layout ribbon is largely devoted to enhancing charts in Power View. The buttons it contains are outlined in Figure 4-19.
Figure 4-19. The Layout ribbon
There are only a few buttons in the Layout ribbon, and they are, fortunately, largely intuitive. Should you need a reference, then a detailed explanation of their use is given in Table 4-1.
Table 4-1. The Layout Ribbon Buttons
|
Button |
Description |
|---|---|
|
Title |
Adds or removes a title from the chart. |
|
Legend |
Lets you choose where (if at all) the legend is placed on a chart. |
|
Data Labels |
Lets you decide to add or hide data labels and choose where they are placed. |
|
Map Background |
Adds a thematic background to a map. This is explained in Chapter 7. |
|
Axis Type |
Lets charts have continuous or interrupted axes. |
|
Grid Height |
Allows you to choose the number of vertical charts that can be displayed if multiples are selected. |
|
Grid Width |
Allows you to choose the number of horizontal charts that can be displayed if multiples are selected. |
|
Axes |
Synchronizes chart axes when tiles are added to a chart. |
|
Colors |
Synchronizes chart colours across the tiles when tiles are added to a chart. |
|
Bubbles |
Synchronizes chart bubble sizing when tiles are added to a chart. |
Enhancing Charts
Now that you have been introduced to the Layout ribbon and have mastered basic charts, it is time to move on to the next step and learn how to tweak your charts to the greatest effect. The next few sections are, consequently, devoted to the various techniques available in Power View to give your charts real clarity and power.
If you have a chart with more than one field that provides the values on which the chart is based, then you will see a legend appear automatically. The default for the legend is for it to be placed on the right of the chart. However you can choose where to place the legend, or even whether to display it at all, by choosing from one of the options that appear when you click on the Legend button in the Layout ribbon.
The available options are given in Table 4-2.
Table 4-2. Legend Position Options
|
Legend Option |
Comments |
|---|---|
|
None |
No legend is displayed for this chart. |
|
Show Legend At Right |
The legend is displayed at the right of the chart. |
|
Show Legend At Top |
The legend is displayed above the chart. |
|
Show Legend At Left |
The legend is displayed at the left of the chart. |
|
Show Legend At Bottom |
The legend is displayed below the chart. |
If one of the legend options is grayed out, it is because this is the option that is currently active.
Legends can require a little juggling until they display their contents in a readable way. This is because the text of the legend is often truncated when it is initially displayed. If this is the case, you have two options:
- Decrease the font size for the chart (as described earlier).
- Modify the chart size.
Do not hesitate to try both these methods, and to switch between the two, as Power View will often end up by displaying the legend in a way that suits your requirements as you adjust these two aspects of the chart display.
Note A legend can contain a scroll bar (vertical for legends to the left or right or horizontal in the case of legends above or below the chart). This can be both extremely useful if you are dealing with many elements in a legend and extremely disconcerting if you are not expecting it!
Each chart is created with a title explaining what the chart is displaying, that is, the fields on which it is based. Here, the available options are fairly simple, as you can only choose between displaying the title or not.
- To hide the chart title, all you have to do is click on the Title button in the Layout ribbon and select None.
- To make a title reappear, click on the Title button in the Layout ribbon, and select Above Chart.
You can always add further annotations to a chart using free-form text boxes. This is described in Chapter 7.
As we have seen already, you can display the exact data behind a column, bar, or point in a line chart simply by hovering the mouse pointer over the data that interests you. Yet there could be times when you want to display the values behind the chart permanently on the visualization. This is where data labels come into play.
To add data labels to a chart (in this example I will use the chart shown in Figure 4-11, which we created previously), all you have to do is
- Click inside the chart to which you wish to add data labels.
- Go to the Layout ribbon.
- Select Outside End from the Data Labels button.
Power View will add data labels to the chart as shown in Figure 4-20.
Figure 4-20. A bar chart with data labels applied
As you will have seen when you were clicking on the Data Labels button, Power View gives you several options concerning the placement of data labels in a chart. These options are explained in Table 4-3.
Table 4-3. Data Labels for Column and Bar Charts
|
Data Label Option |
Comments |
|---|---|
|
None |
No data labels will be superimposed on the chart. |
|
Center |
The data label will be displayed (if possible) inside the data area. |
|
Inside End |
The data label will be displayed (if possible) inside the data area, at the top end of the bar or column. |
|
Inside Base |
The data label will be displayed (if possible) inside the data area, at the top end of the bar or column. |
|
Outside End |
The data label will be displayed (if possible) outside the data area, at the top end of the bar or column. |
When applying data labels to line charts, the possible options are somewhat different than those offered for bar and column charts. Options for line charts are given in Table 4-4.
Table 4-4. Data Labels for Line Charts
|
Data Label Option |
Comments |
|---|---|
|
None |
No data labels will be superimposed on the chart. |
|
Auto |
Power View will place the data labels as it sees best. |
|
Center |
Data labels will be placed across each point on the chart. |
|
Left |
Data labels will be placed to the left of each point on the chart. |
|
Right |
Data labels will be placed to the right of each point on the chart. |
|
Above |
Data labels will be placed above each point on the chart. |
|
Below |
Data labels will be placed below each point on the chart. |
Note When applying data labels to column, bar, and line charts, you will notice that sometimes Power View cannot, physically, place all the data labels exactly where the option that you have selected implies that they should appear. This is because on some occasions there is simply not enough space inside a bar or column at the upper or lower end of a chart, to fit the figures as, the bar or column is too small. In these cases, Power View will place the data outside the bar or column. On other occasions, the data cannot fit outside a line, column, or bar without being placed above the upper end of the axis. Here again, Power View will tweak the presentation to get as close as possible to the effect that you asked for.
There are a few final points to note on the subject of data labels:
- If one of the data label options is grayed out when you click the Data Labels button on the Layout ribbon, it is because this is the option that is currently active.
- Pie charts cannot display data labels, so the Data Labels button is grayed out on the Layout ribbon.
- Scatter charts and balloon charts can also display data labels. However, they will not display figures; instead, they will display the labels (the descriptive text) for the point or balloon in a chart.
Drilling Down
In Chapter 2, we saw that Power View lets you drill down into tables, level by level, to pursue your analyses of the underlying data. Well, it probably comes as no surprise to discover that you can also drill down into the data that is displayed as charts, as well as drill back up again. As an example of this, let’s imagine that you want to take a look at average direct costs and average sale costs. Yet you want to see these
- At the top level, by country
- Then, for a given country, by car age bucket (this is explained in Chapter 10, and is a way of grouping car ages into a set of thresholds)
Let’s see how this can be done.
- Start with a new Power View report, where the report filter is set to allow data only for the year 2013.
- Add the following fields to the FIELDS box in the Field List:
- CountryName (from the Countries table)
- CarAgeBucket (from the SalesData table)
- DirectCosts (from the SalesData table)
- SalesCosts (from the SalesData table)
- Click on the popup triangle at the right of the DirectCosts and SalesCosts fields, and set the aggregation to Average for each of these fields.
- Switch the visualization from Table to Matrix, using the Table button in the Design ribbon.
- Also in the Design Ribbon, click Show Levels, and select Rows, Enable Drill Down One Level At A Time.
- Switch the visualization to clustered column (using the Column button in the Design ribbon). You will see a column chart with only the top level of axes (CountryName) visible. It should look like Figure 4-21. Note that the title is “Average Of DirectCosts, Average Of SalesCosts By CountryName”.
Figure 4-21. The top level in a drill-down chart
- Double-click on either of the columns for the UK. You will drill down to the next axis level, CarAgeBucket. The chart should look like Figure 4-22. Note that the title is now Average Of DirectCosts, Average Of SalesCosts By CarAgeBucket:
Figure 4-22. A lower level in a drill-down chart
- To drill back up to the preceding level, click on the Drill-Up , which appears at the top right of the chart (this is shown in Figure 4-23).
Figure 4-23. The Drill-Up icon in a chart visualization
Drilling up will return you to the initial chart, as seen in Figure 4-20.
Now, the technique that I just explained is not the only way to create a drill-down chart. I would argue that it is probably the easiest to do when you are new to Power View, or if you are testing things out as you try and find a suitable visualization to express your findings in visual form. However, and in the interests of completeness, there is another way that I tend to use in practice. It consists of
- Creating a single-level chart
- Adding a second level (or indeed, several more levels) to this chart
To create a drill-down chart using this alternative approach
- Add the following fields to the FIELDS box in the Field List in a Power View report:
- CountryName (from the Countries table)
- DirectCosts (from the SalesData table)
- SalesCosts (from the SalesData table)
Note AS you can see, you did not add CarAgeBucket (from the SalesData table) yet. Also you need to make sure that the values are set to use the Average aggregation type. - Switch the visualization to a clustered bar chart (to ring the changes a little). You should see a chart like Figure 4-24.
Figure 4-24. A clustered bar chart using average aggregations
- Leaving the chart selected, add the CarAgeBucket (from the SalesData table) to the AXIS box in the Field List Design area, under the CountryName field. The Field List Design area should look like Figure 4-25.
Figure 4-25. The Fields List Layout section for a clustered bar chart and average aggregations
Nothing in the chart has changed; yet, if you double-click on any bar, you drill down to the next level. If you were to try this with Switzerland, for instance, you would see what appears in Figure 4-26.
Figure 4-26. Drilling down inside a chart
You can add multiple levels to the axis of a chart into which you wish to drill down. If you want to change the order of the elements used to provide the levels you use to drill down, then all you have to do is alter the arrangement of the field names in the AXIS box of the field names Design area by dragging them up or down.
You can remove a level in this hierarchy in one of two ways:
- Clicking on the popup icon for the level at the right of the field name and selecting Remove Field
- Dragging the field out of the AXIS box of the field names Design area and up into the Field List area.
As it is all too easy to get lost when using charts with multiple hierarchical levels, I advise you to take a look at the chart title before drilling up or down. This way you can always see which level in the data hierarchy is currently displayed. Also, you can always see if you are at the top of a hierarchy—the drill up icon will never appear at the top right of a chart.
Once you have perfected the appearance of a chart, you could decide to zoom in to the chart for a detailed look. This is incredibly easy:
- Move the mouse pointer over the chart (I will use the one you saw in Figure 4-15). You will see two tiny icons appear over the top right of the chart. You can see these icons in Figure 4-27.
Figure 4-27. The Pop Out icon
- Click on the rightmost of these icons—the Pop Out icon.
The chart will expand to cover the entire area of the Power View report. Not only that, but the following changes will be visible:
- The axis text will be adjusted and should not contain ellipses (unless the text is extraordinarily long).
- The number of elements displayed will be adjusted to attempt to show as much data as clearly as possible. This can mean that any scroll bars that are visible in the chart before you clicked Pop Out could disappear.
- The major gridline on the values axis could change to allow finer increments.
Take a look at the chart you created previously in Figure 4-15; once it has been popped out, you can see that a much clearer representation of the underlying data is available. Indeed, to get the full effect of a pop out, it is probably better to see the entire Power View report, as is shown in Figure 4-28.
Figure 4-28. A popout chart
Note A popout visualization cannot be saved in its expanded version. A Power View report will always open with all visualizations in their normal state. Interestngly, you cannot delete a chart which is expanded, either.
Chart Filters
Any chart can be filtered to show a subset of the data that you want to display. Fortunately applying filters is easy, as all the filtering techniques are identical to those that I described in the previous chapter. So I will assume that you have already taken a look at Chapter 3, and here I will try and build on the knowledge you have already acquired and explain how it can be used effectively when creating chart visualizations with Power View.
To give a practical example of this, let’s take up a point that I made earlier about negative values in pie charts. To avoid giving the idea that negative gross margin is somehow positive, it would be a good idea to display two pie charts that show, respectively, clients where we made money and clients where we lost money in 2013. This is how you can do it:
- Create a new Power View report and set the report filter to include data for the year 2013 only.
- Drag the field ClientName from the Clients table onto the report area.
- Drag the field GrossMargin from the SalesData table onto the table containing the client names.
- Make this table into a pie chart. You will see the warning that the chart contains negative data, as shown in Figure 4-29.
Figure 4-29. A pie chart containing negative values
- Sort the chart by GrossMargin, in descending order (hover the mouse pointer over the chart and when Sort By ClientName, Asc appears at the top of the chart, click ClientName and Asc to switch them to GrossMargin and Desc).
- Click inside this chart (but not on any pie segment) and display the Filter pane (unless it is already visible).
- Click on Chart in the Filters Area, expand GrossMargin, and click on the Advanced Filter Mode icon.
- From the popup Show Items For Which The Value, select Is Greater Than Or Equal To.
- From the box under this selection, enter 0 (zero), and press Enter. This will prevent negative numbers from being displayed.
- Tweak the font size, and resize the chart if you want to, to give it the allure that you prefer.
- Copy the pie chart and ensure that the copy is selected.
- Click on Chart in the Filters Area; GrossMargin should be expanded and in Advanced Filter mode.
- In the popup Show Items For Which The Value in the Filter pane, select Is Less Than 0, and press Enter.
- Sort the values in the second stacked bar chart by GrossMargin, Ascending (as these are negative values, we want to see the biggest loss-maker first).
- Tweak the font sizes and adjust the legend position for the second chart.
- Position the two pie charts on the Power View report.
The report should look like Figure 4-30. I have left a popup visible to remind you that when you have extended data sets, you can always see the exact figures by floating the mouse pointer over a pie segment.
Figure 4-30. Pie charts with separate filters for each chart
Data visualization purists are, I imagine, looking at the pie chart of positive sales and muttering that there are too many elements for a single pie chart and that there are too many clients for whom the sales figures are too small to be read easily. I agree, and the solution is to split the pie chart of positive values into two charts: one for major clients, and one for the smaller clients. I will also set the chart of all middle-sized clients to be a bar chart, as there are (in my opinion) too many elements for a pie chart. So, to separate out the clients with sales under 200,000.00 (I came up with this figure by looking at the sales figures in the popup for the pie slices; I chose what seemed to be a good break point between major sales and lower sales figures), we will extend the chart filter used previously to set a filter of upper and lower boundaries for the data in the chart:
- Click on the initial pie chart for positive sales.
- Click on Chart in the Filter pane.
- Change the value for Is Greater Than Or Equal To to 200000, and press Enter.
- Copy the initial pie chart for positive sales and ensure that the copy is selected.
- Convert this chart to a clustered bar chart by selecting Clustered Bar from the Bar Chart button in the Design ribbon. This is to show you that filters are applied independently of the chart type.
- In the Filter pane for the third (clustered bar) chart, change the value for Is Greater Than Or Equal To to 0, and press Enter.
- Click And under the box where you entered the value 0.
- Select Is Less Than Or Equal To from the second popup.
- From the box under this selection, enter 200000, and press Enter.
- Set the Chart Type to Clustered Column.
- Tweak the layout of all three charts until you have a telling presentation.
An example of how the data can be filtered to create three separate charts is given in Figure 4-31.
Figure 4-31. Individual charts with separate filters
I have jumped ahead slightly here by adding some explanatory text boxes so that the reader can see what each chart is showing. These are explained in Chapter 7 if you want to flip a few pages and find out. However what matters here is that you have seen how to fine-tune individual visualizations so that they display only the data that you want them to show.
Conclusion
The techniques described in this chapter should help you produce a real “wow” effect on your audience. You can now deliver punchy presentations where crisp clear charts help you make your point with definite panache.
We have seen how to create a set of basic chart types (pie charts, bar charts, column charts, and line charts) using one or more data values. We have also seen how to filter charts using the same filtering techniques that you learned previously.
However, charts do not end at this in Power View. There is a further range of more advanced charting possibilities that you can learn to exploit; they are the subject of the next chapter.