Using Pivot Tables

A pivot table is useful for summarizing and analyzing large amounts of data. It shows measure values for the intersection of two dimensions.

Pivot tables and heat maps display data in a similar tabular fashion. Use a pivot table if you want to further analyze the data in the visual, for example by changing the column sort order or by applying aggregate functions. Use a heat map if you want to identify trends and outliers, because the use of color makes them easier to notice.

To create a pivot table, choose at least one field of any data type. Usually, you add a measure and two related dimensions.

You can add up to 20 fields as rows and 20 fields as columns. Up to 500,000 records are supported.

Pivot tables provide several useful functions:

  • Specify multiple measures to populate the cell values of the table so that you can see a range of data.
  • Cluster columns and rows to show values for subcategories grouped by a related dimension.
  • Sort values in rows or columns.
  • Apply statistical functions.
  • Add totals and subtotals to rows and columns.
  • Use infinite scroll.
  • Create custom total aggregations.

To reduce the level of complexity and keep the pivot table easy to use and understand, try to include a minimal set of rows, columns, metrics, and table calculations.

To create a pivot table:
  1. Click Dashboards tab, the Dashboards tab.
  2. Click Dashboard Designer, in the bottom-left corner.
  3. On the Analyses dialog, click New analysis or click Options button, the Options button, next to an existing analysis and select Edit.
  4. On the analysis page, click , the Add visual button, on the toolbar.
    Analysis page - Create a visual
  5. Click the Pivot table icon.
    Pivot table icon
  6. Add data to the visual by dragging fields from the Data panel to the appropriate field wells in the Visuals panel:
    • To create a single-measure pivot table, drag a dimension to the Rows field well, a dimension to the Columns field well, and a measure to the Values field well.
    • To create a multi-measure pivot table, drag a dimension to the Rows field well, a dimension to the Columns field well, and two or more measures to the Values field well.
    • To create a clustered pivot table, drag two or more dimensions to the Rows field well, two or more dimensions to the Columns field well, and a measure to the Values field well.
    You can also select multiple fields for all the pivot table field wells to combine the multi-measure and clustered approaches.
    Note: To view roll-ups for calculated fields, make sure that you use aggregates. For example, a calculated field with field-1 / field-2 does not display a summary when rolled up. However, sum(field-1) / sum(field-2) does display a roll-up summary.
  7. To collapse a group of rows or columns in a clustered pivot table, click Collapse icon, the Collapse icon, next to a field name.
    You can expand a collapsed group by clicking Expand icon, the Expand icon.
  8. To hide rows, columns, and values from a pivot table, click Options button, the Options button, next to a field in the Visuals panel and select Hide from the menu.
    You can show all the hidden fields by clicking Options button, the Options button, next to any field and selecting Show all hidden fields.
  9. To change columns to rows and rows to columns, click Swap rows and columns icon, the Swap rows and columns icon, in the top-right corner of the pivot table.
  10. To adjust the row height, hover over the horizontal lines between rows until the resize cursor appears, and then drag the lines to a new position.
  11. To adjust the column width, hover over the vertical lines between columns until the resize cursor appears, and then drag the lines to a new position.
  12. Customize the appearance of the visual in the Properties panel on the right.
    1. Under Display Settings, format the title and subtitle.
      For more information, see Formatting Titles and Subtitles in Visuals.
    2. Under Pivot options, set the layout of the pivot table.
      • Under Layout, select Hierarchy or Tabular. In hierarchical pivot tables, all row fields are displayed in a single column labeled Rows. In tabular pivot tables, each field in the Rows field well has a separate title cell.
      • Under Values position, select Columns or Rows. By default, pivot tables have a columnar format. When you change to a row-based format, a column with the value name is added to the right of the row header column.
      • To hide the collapse and expand icons from the pivot table, click Hide +/– buttons icon, the Hide +/– buttons icon, next to +/– buttons.
      • To hide the columns that only have a single metric value, click Hide single metric icon, the Hide single metric icon, next to Single metric.
      • To automatically hide all collapsed columns in a tabular pivot table, click Hide collapsed columns icon, the Hide collapsed columns icon, next to Collapsed columns.
    3. Under Headers, format the row and column headers.
      • For row headers in a hierarchical pivot table, to hide or display the rows label, click Hide icon, the Hide icon, or Show icon, the Show icon, next to Rows label. You can also enter a custom label and enable the Style rows label option if you want to also apply the formatting settings to the rows label.
      • For row headers in a tabular pivot table, you can enable the Style field names option if you want to also apply the formatting settings to the field names.
      • For column headers, to hide or display the column names, click Hide icon, the Hide icon, or Show icon, the Show icon, next to Column field names. You can also enter the height of the header rows in pixels under Row height (pixels). Enter a whole number from 8 through 500.
      • Customize the text color, size, and alignment, the background color, and the border settings.
    4. Under Cells, format the pivot table cells.
      • Under Row height (pixels), enter the height of the pivot table rows in pixels. You can enter a whole number from 8 through 500.
      • Under Column width (pixels), enter the width of the pivot table columns in pixels. You can enter a whole number from 25 through 1000.
      • To expand the pivot table so it fills the current view, click Expand to view.
      • Under Background, set the color of the background. You can enable the Alternate row colors option to set different colors for odd rows and even rows.
      • Customize the text color, size, and alignment and the border settings.
    5. Under Total and Subtotal, format row and column totals and subtotals.
      For more information, see Adding Totals and Subtotals to Pivot Tables.
    6. Under Row names, Column names, and Value names, customize the names of the rows, columns, and values.
      Enter a custom name in the text box. To include a parameter in the name, click Insert parameter icon, the Insert parameter icon.
These limitations apply to pivot tables:
  • You can add any combination of row and column field values, up to a total of 40. For example, if you have 10 row field values, then you can add up to 30 column field values.
  • You can use pivot table calculations only for non-aggregated values. For example, you cannot add a pivot table calculation to a calculated field that is a sum of a measure.
  • If you are sorting by a custom metric, you cannot add a table calculation until you remove the custom metric sort. If you are using a table calculation and then add a custom metric, you cannot sort by the custom metric.
  • Totals and subtotals are blank for table calculations on metrics aggregated by distinct count.

In this section:

  1. Adding Totals and Subtotals to Pivot Tables
    Pivot tables can display totals and subtotals on rows and columns.
  2. Adding Table Calculations in Pivot Tables
    You can use table calculations to apply statistical functions to pivot table cells that contain numeric values.
  3. Sorting Data in Pivot Tables
    In pivot tables, you can sort rows and columns independently of each other in alphabetical order, or by a measure. You can also sort the data based on a field that is not included in the pivot table.
Parent topic: Creating Visuals