Pivot table

The pivot table presents dimensions and measures as rows and columns in a table. In a pivot table you can analyze data by multiple measures and in multiple dimensions at the same time. You can rearrange the measures and dimensions to get different views of the data. The activity of moving measures and dimensions interchangeably between rows and columns is known as 'pivoting'.

Example:  

The efficiency of a pivot table can be illustrated by comparing a regular table with a pivot table that has the same data. In the following table, you have three dimensions: Customer, Product Group, and Item, and two measures: Quantity and Sales. The table shows the sales of some food products. If you want to rearrange the data to simplify analysis, the options are somewhat limited. You can change the order of the columns, but that does not improve the overview. You can also set the sorting order, either in the sorting section in the properties panel, or by clicking the dimension columns. However, the problem persists. The customers, product groups, and items are all presented more than once, and it is not possible to get a good summary of the data.

Here is the same data in a pivot table.

As you can see, the pivot table presents the data in a much more condensed way, which simplifies analysis and comparison. Compared to the regular table, the number or rows has been halved in the pivot table and the number of columns is three instead of five.

One of the advantages of a pivot table is the interchangeability, that is, the ability to move row items to columns and column items to rows. This flexibility is very powerful and enables you to rearrange the data and have several different views of the same data set. Depending on what you want to focus on, you move the dimensions and measures to bring forward data of interest and hide data that is either too detailed, or irrelevant for the analysis.

The pivot table shows the dimensions Customer, Product Group, and Item, and the measures Quantity and Sales. In this view, you have a summary of quantity and sales for each customer. If you want to know which items and product groups that the customers bought, you need to expand the customer fields by clicking P. A P icon indicates that a field can be further expanded and present more details, while a Q icon indicates that the field can be collapsed, to reduce the number of fields and details.

Pivoting

When you want to rearrange your data to get a new view, you drag the items to the new place, either to a column or a row. In the following pivot table, the dimension Customer has been dragged to the position after Product Group and the dimension Item to the position before Product Group. As a consequence, the dimensions are now sorted by Item, primarily. Focus has shifted from Customer to Item. By expanding the dimensions you can find out the quantities and sales for each customer, but there is another way to achieve that goal.

By moving the dimension Customer from rows to columns, you retain focus on the dimension Item, but you also get the distribution of items per customer. The move has made the pivot table more information dense.

Measure grouping

As you may have noticed, Quantity and Sales are not presented as separate measures in the top column row. Next to the dimension Customer, you find an item called Measures. When you use more than one measure, the measures are automatically grouped together forming a measure group, Measures, which as a whole can be added to the rows section or the columns section. The measure group is not editable in the table. You cannot split the measure item and use one measure as a row and another as a column, nor can you change the order in which the measures are presented. Changes to the Measures item are made in the properties panel.

Different ways of pivoting

Essentially, pivoting involves dragging the dimensions and measures from rows to columns and columns to rows, but you have two options for performing the pivoting.

• In the pivot table (both when editing and when analyzing).
• In the properties panel (only when editing).

Pivoting using the properties panel

In the properties panel, you can add measures and dimensions to the pivot table, and also move the dimensions and measures to rows or columns. When you use more than one measure, the measures are grouped and a Measures item is created. You can change the internal order of the dimensions and measures, but when you have more than one measure, it is always the whole measure group that you move.