Skip to main content Skip to complementary content

Creating pivot tables

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 create a new pivot table on the sheet you are editing.

  1. In a sheet, click to add a new visualization.
  2. Under Visualization, select Pivot tablet.
  3. Click Add and select a field or master dimension for the row dimension.

    Optionally, add more dimensions as rows. The pivot table drills down rows in the order they are added. You can click and drag the dimensions to reorder them.

  4. Optionally, click Add and select a field or master dimension for a column.

    The pivot table drills down the column dimensions in the order they are added. You can click and drag the dimensions to reorder them.

  5. Click Add and select a field or master measure for the measure.

    Optionally, add more measures.

You can adjust appearance and other settings in the properties panel.

Tip noteColumn width is automatically set to keep columns together for improved readability. You can adjust the width of the dimension column by dragging the header divider. The width of individual measure columns will still be automatically set. Double-click the header divider to reset to automatic column width.

Pivoting your data in the properties panel

In the properties panel, you can add measures and dimensions to the pivot table, and also pivot rows or columns.

Data

In the data pane, you can add dimensions and measures. You can move items between rows and columns. You can also change item order inside rows or columns. When you use more than one measure, they are grouped and a Values item is created.

Properties panel, Data section.

Sorting

On the sorting pane in advanced properties, you can change the internal order of dimensions and measures.

Properties panel, sorting pane.

Sort by first measure

If you have more than one measure, a measure group is created. Sort by first measure will sort dimension values by the numeric value of the first measure. This sort order will affect all dimensions, and take precedence before any other sort order defined under the dimensions.

For example, you have two measures: Quantity and Sales. In the properties pane, under Data> Measures, Quantity is listed first. If you Sort by first measure in descending order, your table is sorted starting with the dimension with the highest Quantity.

Pivot table in Edit mode. Sort by first measure has been toggled on.

Pivot table with sorted by first measure.

Limitations:

  • This option is only supported if subtotals are calculated. You can calculate subtotals by doing one of the following:
    • In the properties pane, go to Data, and click on a dimension. Toggle on Show totals.
    • In the properties pane, go to Appearance > Presentation. Toggle on Indent rows.
  • This option is not supported for calculated dimensions.
  • This option is only supported if all dimensions are in the Row section and all measures are in the Column section.

Sort by expression

When sorting by expression, the expression is only applied to the first dimension of a pivot table. Subsequent dimensions are sorted in the same order as the first dimension.

Global grouping

Global grouping lets you create a limited data set, and within that data set, single out values that you want to focus on. For example: the best quarters, the top sales people, or the worst selling products.

Example:  

In the following pivot table, no limitation is applied. The values are sorted on Sales, descending. The list is long and the values for 2013 are not shown.

Pivot table, no limitation is applied.

In the following pivot table, a limitation has been applied to the (inner) dimension Sales Rep Name, so that only the top five sales representatives for the years 2013 and 2014 are shown.

Pivot table with limited inner dimension

The next step is to select global grouping in the advanced edit mode properties panel. The option Global grouping is only available after you have applied a limitation on the dimension.

When global grouping is selected, the limitation on the top five sales representatives is applied again, but this time the dimension Year is ignored. The five sales representatives with the highest sales (either in 2013 or 2014) are the only ones that will be presented in the final pivot table.

The following image shows the six highest results for 2014 and 2013. The top four results are from 2014, but the fifth (John Greg) is from 2013. Because five other sales representatives have higher sales than Brenda Gibson (who was number five in 2014), she is removed.

Six highest results for 2014 and 2013.

The following image shows the pivot table with global grouping applied. The pivot table only contains the sales results for the top five sales representatives. Even though Brenda Gibson had a better result in 2014 than John Greg, his result for 2013 qualified him for the top five list.

Pivot table with global grouping applied.

Creating a bookmark with an expanded pivot table

By default, if you create a bookmark that contains a pivot table, the pivot table will be shown collapsed. If you expanded any rows using , they will not be shown. However, you can choose to show the pivot table as expanded.

  1. Click Bookmark in the toolbar.

  2. Click Create new bookmark.

    Change the name and description, if desired.

  3. Toggle on Save layout.
  4. Click Save.
Related learning:

Did this page help you?

If you find any issues with this page or its content – a typo, a missing step, or a technical error – let us know how we can improve!