A summary table, also called a pivot table, uses groups and statistics to transform your raw data into a more accessible format. A summary table can 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 even further to gain valuable insights that aren't available in the raw table format.
You can copy a table card onto another page by dragging it to the New Page tab or an already created page. If you want to copy the table onto the same page, use Ctrl+C to copy and Ctrl+V to paste.
Create a summary table
To create a new summary table, complete the following steps:
- Select the fields to display in the summary table. String, number, and rate/ratio fields are supported in summary tables. If a location field is chosen, the display field will be used in the summary table. Date/Time fields cannot be used in summary tables.
There is no limit to the number of fields that can be used in a summary table.
You can search for fields using the search bar in the data pane.
- Create the summary table using the following steps:
- Drag the selected fields to a new card.
- Drop the selected fields on the Table drop zone.
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) will be displayed 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
Use the following steps to add a field:
- Select the fields you want to add to the summary table.
- Drag the fields to the body of the summary table.
The Add new field drop zone appears.
- Drop the fields on the drop zone.
The summary table is updated to display the new fields.
The fields will be added in the last position for the string or numeric fields by default. Alternatively, you can drop the fields in 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
Use the following steps to remove a field:
- Click and drag the field header onto the summary table.
The Delete field drop zone appears.
- Drop the field onto the drop zone.
If you do not want to use drop zones, you can delete a field by expanding the menu beside the field name and clicking Delete field from the list.
Update a field
Use 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 is displayed 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 your 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. If you wanted to find the sales per region within each state you would select state, region, and sales fields to create your table.
You can reorder your table columns by dragging a column header and dropping it in 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 Sort Fields . You can sort a single column, or sort multiple columns in the table using Shift+click on multiple fields. When using a grouped summary table, sorting occurs on the sorted column within the group.
You can apply a card-level filter to your summary table if you want 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 will be displayed in the summary table.
Applying a card filter will not affect other cards using the same dataset.
Features in summary tables can be selected by clicking a single feature, by clicking and dragging the pointer over the desired features, or using 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 a selection has been made on a summary table, the following options will be available:
- Invert a selection—Click the Invert selection button to switch your selected features. The inverted selection will be reflected in all other cards displaying the same data.
- Show a selection—Click the Show selection button to show only your selected features in the summary table. The features that are not selected are temporarily removed from the summary table. The selected features will remain selected on all other cards displaying the same data, but no other changes will take place on those cards. You can show the unselected features again by clicking Show selection a second time. The selected features will remain selected until you click the scroll bar in the summary table or in another card.
Making a selection is different from applying a filter, because selections are more temporary and are reflected on all cards using the same dataset.
A summary statistic can be chosen for each number or rate/ratio field being summarized. The available statistics include sum, minimum, maximum, average, median, and percentile. The percentile is customized using the text box on the menu. For example, to view the 90th percentile, enter 90 in the text box.
The statistic will be calculated for each row on the summary table, as well as the entire dataset. The dataset statistic is displayed as a footer at the bottom of 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 will be calculated using all 10,000 features, rather than averaging the 5 rows displayed on the table.
Copy a table
A table card can be duplicated on a page by activating the card (a card is active when the Action button is visible) and using Ctrl+C to copy the card and 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 new page, the dataset will also be copied, if necessary, along with all of the processes used to create the table. The dataset will not be copied if it already 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.