Use pivot tables

Prerequisites

  • You have the Insights Author license.

Use pivot tables to show measure values for the intersection of two dimensions.

Pivot tables are similar to heat maps. Use a pivot table if you want to analyze data on the visual. Use a heat map if you want to identify trends and outliers because the use of color makes these easier to spot.

To create a pivot table, choose at least one field of any data type, and choose the pivot table icon. Insights creates the table and populates the cell values with the count of the column value for the intersecting row value. Typically, you choose a measure and two dimensions measurable by that measure.

Pivot tables support scroll down and right. You can add up to 20 fields as rows and 20 fields as columns. Up to 500,000 records are supported.

Using a pivot table, you can do the following:

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

As with all visual types, you can add and remove fields. You can also change the field associated with a visual element, change field aggregation, and change date field granularity. In addition, you can focus on or exclude rows or columns. For more information about how to make these changes to a pivot table, see Changing fields used by a visual in Insights.

For information on formatting pivot tables, see Format a visual in Insights.

For information on custom total aggregations for pivot tables, see Customize how totals in tables are calculated.

Topics in this section

Pivot table features

Pivot tables don't display a legend.

The following table lists the actions you can do with pivot tables.

Feature Supported? Comments For more information
Change the legend display No   Legends on visual types in Insights
Change the title display Yes   Titles and subtitles on visual types in Insights
Change the axis range Not applicable   Range and scale on visual types in Insights
Change the visual colors No   Colors in visual types in Insights
Focus on or exclude elements Yes, with exceptions You can focus on or exclude any column or row, except when you are using a date field as one of the dimensions. In that case, you can only focus on the column or row that uses the date dimension, not exclude it.

Focus on visual elements

Exclude visual elements
Sort Yes You can sort fields in the Rows or Columns field wells alphabetically or by a metric in ascending or descending order.

Sorting visual data in Insights

Sort pivot tables in Insights

Perform field aggregation Yes

You must apply aggregation to the field or fields you choose for the value. You can't apply aggregation to the fields that you choose for the rows or columns.

If you create a multi-measure pivot table, you can apply different types of aggregation to the different measures. For example, you can show the sum of the sales amount and the maximum discount amount.

 Changing field aggregation
Add drill-downs No   Adding drill-downs to visual data in Insights
Show and hide totals and subtotals Yes

You can show or hide totals and subtotals for rows and columns.

Metrics automatically roll up to show subtotals when you collapse a row or column. If you use a table calculation, use aggregates to display roll-ups.

  
Export or copy data Yes

You can export all of the data to a CSV file.

You can select and copy the content of the cells.

 Export data from visuals
Conditional formatting Yes You can add conditional formatting for values, subtotals, and totals. Conditional formatting on visual types in Insights

Related topics