Microsoft® Excel® 2013: Using Power View

Understanding table, matrix, and cards

Now that you have learned the basics of Power View, it is time to delve a bit deeper into the different tools you can use to show data. As you have seen, you always start building your report with a table, which is the default visualization that Power View uses when you start adding table columns to the report.

Even if you always start with a table, you can then switch to different tabular or graphical visualizations and, in this section, you are going to explore them in more detail. After you select a table, you can change the visualization using the Switch Visualization button group on the DESIGN tab of the Excel ribbon, as shown in Figure 10-12.

Figure 10-12. The Switch Visualization button group lets you change the visualization of data.

The Table button contains three visualizations: Table, Matrix, and Card. Table is the visualization that you are already familiar with; it shows data in a simple, tabular format. Matrix is more like a PivotTable, showing data sliced into rows and columns and highlighting the hierarchical structure of your model.

Using the matrix visualization

In Figure 10-13 you can see a matrix visualization where the Territory hierarchy is placed on rows and the Year on columns, and the Sales is the value to show.

Figure 10-13. The Matrix visualization looks more like a PivotTable than a simple table.

The matrix visualization is remarkable, but due to the limited real estate available on a single page, it might be somewhat dispersive because not enough data will fit into the report. A good option to increase the number of rows and columns shown in a report is to remove the totals. On the DESIGN tab of the Excel ribbon, you have the option to show totals for rows, columns, both, or neither of them, as shown in Figure 10-14.

Figure 10-14. You can choose to show totals for rows, columns, both, or neither for a Matrix view.

For example, in Figure 10-15, you can see the same report without the totals, which looks much more compact and easy to read.

Figure 10-15. Removing totals, the Matrix view is much more compact and easier to read in Power View.

The most interesting feature of a Matrix view without the subtotals is the ability to put a table column (the year, in this example) as a column header. In fact, in a normal table, you can only slice using the rows, whereas a matrix lets you slice on columns too, resulting in a convenient way to show data for different time frames.

Using the card visualization

The third table visualization is the Card view. When data is shown in Card mode, each row is like an index card. Card view is great to use when your dataset contains pictures. For example, in Figure 10-16, you can see a report showing products and sales in card format.

Figure 10-16. Card view is effective when a table has a picture in a column, like the Products table.

Values are automatically arranged in rows and columns based on the width of the table. The smaller the width, the larger the number of rows used for the card. If a picture is added to the card, then it is shown in the first position, making it very visible.

The heading of Card view is the table default label, if there is one. You can set the table default label in the PowerPivot window, using the Table Behavior button on the Advanced tab of PowerPivot. The Table Behavior button opens a window that you can use to set a few properties for the table, among which you will find the Default label and Default image. You will learn more about the default behavior in the next chapter, but for now, it is enough to remember that the header of the Card view can be set by selecting a column as the table default label.

In Figure 10-17, you can see the Table Behavior dialog box for the product table.

Figure 10-17. The Table Behavior window contains, among other properties, the default label of the table.

Using a table as a slicer

Previously in this chapter, you saw that a chart can be used to filter the report automatically by clicking one of the items of the chart. Table visualizations do not have this property. You can click and move the cursor on the rows of a table, but this operation does not apply any filtering to the report.

Nevertheless, you can use a table as a filter by converting it into a slicer. Not all the tables can be converted into slicers, though. If you want to turn a table into a slicer, it needs to contain a single column. Once you have created a table containing a single column, you can convert it into a slicer using the Slicer button on the DESIGN tab of the Excel ribbon.

Once a table has been converted to a slicer, you will not be able to add columns and calculated fields to it anymore because a slicer contains only a single column. Nevertheless, you can always convert it back to a table visualization. At that point, you will be free to modify its content.

In Figure 10-18, you can see a Card view with two slicers: one for the color and another one for the model name. Please note that the slicers affect each other. In fact, once the model name has been selected, only the valid colors for the selected model name become available for further selection.

Figure 10-18. Slicers are useful for dynamic reports.