Microsoft® Excel® 2013: Using Power View

Using charts

The table visualizations are interesting because they provide detailed information of data, but they lack the immediate insights that you can get by looking at a chart. For this reason, when working with summary reports, you will probably use many charts and only a few tables containing detailed data. Charts are probably the most exciting feature of Power View because they are immediately useful and provide a great visualization of complex information and relationships.

The most commonly used charts are bar and column charts, which you can select by using the correspondent buttons in the Switch Visualization button group of the DESIGN tab of the Excel ribbon. Charts can be used as automatic slicers by simply clicking a value. For example, in Figure 10-19, you can see two charts (one column and one bar) where the user clicked the Black bar in the Sales by Color chart. The charts clearly show both the global value and the selected ones very effectively.

Figure 10-19. Bar and column charts are useful for both visualizing and slicing data.

The choice between whether to use a column chart or a bar chart really depends on the kind of data you are analyzing.

Using the line chart

Another chart visualization is the line chart, which is mainly used to show the behavior of a measure over time. You can place many measures on the same chart. For example, in Figure 10-20, you can see a simple line chart showing sales and revenues for several months.

Figure 10-20. Line charts are useful to show the behavior of a measure over time.

Using the pie chart

Another type of chart visualization is the pie chart. Many professionals discourage the use of pie charts because they are not easy to read at first glance. They make it difficult to compare the relative size of different sections of a given pie chart or to compare data across different pie charts. Nevertheless, pie charts are very popular, and many users request them because they want to compare the size of a single slice against the entire pie.

For example, in Figure 10-21, you can see a simple pie chart that shows the contribution of different categories to total sales.

Figure 10-21. Simple pie charts are compact and might be a good representation for simple data.

Pie charts in Power View can be simple or sophisticated, depending on the kind of data you want to show. You have the option to select one column to define the slice size and a different column to color them. Moreover, when slicers are active in the chart, the pie highlights the selected area, as other charts do.

In Figure 10-22, you can see a sophisticated pie chart that uses the territory group to define the color of the slices. The slices are then created using the territory country, resulting in many slices rendered with the same color. The interesting fact is that when you click a year to select one, the inner highlighted part shows the contribution for that year and for each country to the total of the territory group.

Figure 10-22. Pie charts can be rather sophisticated, with the ability to illustrate several subcategories within larger pieces of the chart

It is worth noticing that the bar chart used as a slicer is graphically placed inside the pie chart in Figure 10-22, resulting in a single, interactive, and compact report.

Using the scatter chart

The scatter chart (also known as the bubble chart, in its more advanced format) is probably the most fascinating (and most complex) feature among the charting capabilities of Power View. Scatter and bubble charts are a great way to display a lot of related data in one chart. In this type of chart, the x-axis displays one measure and the y-axis displays another, making it easy to see the relationship between the two values for all the items in the chart.

In a bubble chart, a third measure controls the size of the data points. Figure 10-23 displays an example of the visualization power of bubble charts.

Figure 10-23. A bubble chart shows many related pieces of information in a single visualization.

To create this chart, you need to use the sales on the y-axis, the number of products on the x-axis, and the product model name as the detail, and the bubble size reflects the size of profits. By reading the chart, it is easy to see that the top seller is Mountain-200, the biggest bubble of all, thus producing the most of the revenues. It is also interesting to compare the Mountain-200 bubble with the Road-650 bubble. The Road-650 model has many more products, but it sells less and produces less revenues.

Working with bubble charts is not easy. It will take some time before you find a good way of showing the numbers in a chart, but it is worth spending that time. The final representation is compact, yet informative. Moreover, scatter charts have another amazing feature, which we can only try to describe here. We strongly suggest that you look at the chart in the companion workbook and play around with it, because words simply cannot describe the visual experience of looking at it.

In a bubble chart, you can drop a calendar attribute into the play area. It can be the year, the month, or the date, depending on the granularity you want to look at. For example, in Figure 10-24, we put the year in the play axis. At this point, the chart shows data for only the year 2008, the last year for which there are data, but a stimulating play button appears in the lower-left corner of the chart.

Figure 10-24. Using the play axis, there is a play button in the bubble chart.

Clicking the play button starts an animation that shows how values changed over time, resulting in a sort of race between the bubbles to reach the best position. In Figure 10-25, you can see the animation at the point when it reached 2007.

Figure 10-25. When the animation arrives at 2007, it shows a different layout.

As already stated, there is no way to express in just words the feeling of looking at this effect, so stop reading at this point and play with the animation on your PC. We are sure that the experience will push you to study this chart and all its options further.