Pivot table 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.