A summary table, also called a pivot table, uses groups and statistics to transform your raw data into a more accessible format. You can use a summary table to calculate counts and statistics—including sum, average, minimum, maximum, median, and percentile—for unique categories in your data using the number fields in your dataset. The same number field can be used multiple times to calculate all of the available statistics. Subgrouping and sorting features allow you to explore your data further to gain insights that aren't available in the raw table format.
You can copy a table card to another page by dragging it to the New page tab or an existing page. To copy the table to a different position on the same page, press Ctrl+C to copy and press Ctrl+V to paste.
Create a summary table
To create a summary table, complete the following steps:
- Select the fields to display in the summary table.
There is no limit to the number of fields that can be used in a summary table. String, number, and rate/ratio fields are supported in summary tables. If you use a location field, the display field is used in the summary table. Date/time fields cannot be used in summary tables.
You can search for fields using the search bar in the data pane.
- Drag the selected fields to the Table drop zone on a new card to create the summary table.
You can also create summary tables using the Table button above the data pane or the Visualization type button on an existing card. Only compatible visualizations (including maps, charts, or tables) are listed in the Visualization type menu.
Add and remove fields
Fields in a summary table can be added, removed, or updated to display a different field or statistic.
Add a field
Complete the following steps to add a field:
- Select the fields you want to add to the summary table.
- Drag the fields to the Add new field drop zone that appears in the body of the summary table.
The summary table is updated to display the new fields.
The fields are added in the last position for the string or numeric fields by default. Alternatively, you can drag the fields to the header to choose a different position.
You can drag the same number or rate/ratio field to a summary table multiple times to display the field with all available statistics.
Remove a field
To remove a field, drag the field header to the Delete field drop zone that appears in the summary table.
If you do not want to use drop zones, you can delete a field by expanding the menu next to the field name and clicking Delete field in the list.
Update a field
Complete the following steps to update a field:
- Click the arrow next to the field name or statistic type (statistics are available for number and rate/ratio fields only).
A menu appears with compatible fields or statistics.
- Click the field or statistic you want to display.
The field is updated to display the chosen field or statistic.
String fields can only be updated to other string fields. Number and rate/ratio fields can be updated to either a number or a rate/ratio field.
When using two or more string fields to create a summary table, the columns are created in the order you select the fields in the data pane. The order of the string fields affects how the categories are grouped. For example, to find the sales per region in each state, select state, region, and sales fields to create the table.
You can reorder your table columns by dragging a column header to the desired position. All string fields must appear in the first positions of the summary table, followed by the number and rate/ratio fields.
Data in a summary table can be organized using the sort buttons . Multiple fields can be used to sort a summary table. However, only one number field can be sorted at a time.
A summary table is automatically sorted alphabetically by the first string field.
For grouped summary tables, you can use a number field to provide additional sorting criteria. The number field applies the sorting criteria in each group.
To sort the table by a different field, such as a number field, you can use the sort buttons to remove the sort criteria on the string field and add sort criteria on a different field. The summary table is not grouped when sorted only by a number field.
You can apply a card-level filter to the summary table to remove unnecessary text, number, or date values. To apply a filter, click Card filter on the summary table and choose the field you want to filter. You can apply multiple filters to the same table. Only data that meets the criteria for all filters is displayed in the summary table.
Applying a card filter does not affect other cards using the same dataset.
You can select features in summary tables by clicking a single feature, clicking and dragging the pointer over the features, or pressing Ctrl+click. The selected features will be represented in the summary table as well as other maps, charts, or tables displaying the same data. Once you select the features in a summary table, the following options become available:
- Invert selection —Click this button to switch the selected features. The inverted selection is reflected in all other cards displaying the same data.
- Show selection —Click this button to show only the selected features in the summary table. The features that are not selected are temporarily removed from the summary table. The selected features remain selected on all other cards displaying the same data, but no other changes occur on those cards. Click Show selection a second time to show the unselected features. The selected features remain selected until you click the scroll bar in the summary table or on another card.
Making a selection is not the same as applying a filter; selections are temporary and are reflected on all cards using the same dataset.
You can choose a summary statistic for each number or rate/ratio field being summarized. The available statistics are sum, minimum, maximum, average, median, and percentile. You can customize the percentile statistic using the text box on the menu. For example, to view the 90th percentile, type 90 in the text box.
The chosen statistic is calculated for each row on the summary table, as well as the entire dataset. The dataset statistic is displayed as a footer in the summary table.
The dataset statistic is calculated using the raw dataset, rather than the values from the summary table. This distinction is most important for the average statistic. For example, if you have a dataset with 10,000 features and you create a summary table that has 5 rows, the dataset average is calculated using all 10,000 features, rather than averaging the 5 rows in the table.
Copy a table
You can duplicate a table card on a page by activating the card (a card is active when the Action button is visible) and pressing Ctrl+C to copy the card and pressing Ctrl+V to paste it on the page.
A table card can be copied to a different page by dragging the card to the New page tab or to an existing page. When a table is copied to a different page, the dataset is also copied, if necessary, along with all of the processes used to create the table. The dataset is not copied if it exists on the new page unless one of the datasets contains a dataset filter, a calculated field, or a location field created by enabling location.