Create and use a summary table

A summary table, also called a pivot table, uses groups and statistics to transform 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.

Example

A marketing researcher is planning a campaign to promote colleges with a high return on investment. The researcher creates a summary table showing the average cost of colleges for each region in the United States to see how the costs compare across regions.

Create a summary table

Complete the following steps to create a summary table:

1. 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.

   Tip:

   You can search for fields using the search bar in the data pane.

2. Create the summary table using the following steps:
   1. Drag the selected fields to a new card.
   2. Hover over the Table drop zone.
   3. Drop the selected fields on Summary Table.

Tip:

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.

Usage notes

This visualization creates a result dataset in the data pane, which includes the fields used to create the chart. The result dataset can be used to create additional visualizations, rename the fields on the chart axes or in the pop-ups, or apply filters to the chart.

Note:

If you need to add a calculated field to a summary table, the field must be calculated in the original dataset used to create the summary table, not the result dataset. The field will appear in the result dataset after it is added to the summary table.

You can choose a summary statistic for each number or rate/ratio field being summarized. The available statistics are sum, minimum, maximum, average, median, percentile, and running total.

Note:

Median and percentile are not available for certain remote feature layers. If the remote feature layer does not support median or percentile, you can copy the layer to your workbook.

Running total is not available for remote datasets or database connectors without WindowFunction enabled.

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.

Running total calculates the cumulative sum for each field in the order they appear in the table, as follows:

Day	Field sum	Field running total
Monday	1	1
Tuesday	4	5
Wednesday	1	6

Running total requires at least one category field and one number field in the summary table. It is best practice to choose a category that will apply a temporal order to the table (for example, day of month) to give meaning to the running total and to choose a number field that does not already have cumulative values (for example, daily sales). If there is more than one category field, a Partition By field can be selected from a menu. If the calculation is partitioned (a field is selected), the running total will be calculated for each category in the field separately. If the calculation is not partitioned (None is selected), the running total will be calculated for the entire field.

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. A dataset statistic is not displayed for running total.

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.

Use the Card filter button to remove any unwanted data from the card. Filters can be applied to all string, number, rate/ratio, and date/time fields. A card filter does not affect other cards using the same dataset.

Use the Invert selection button to switch all selected and unselected features. Invert selection is only enabled when there are features selected on the table.

Use the Show selection button to show only selected features in the table. Show selection is only enabled when there are features selected on the table.

Use the Visualization type button to switch directly between a summary table and other visualizations, such as a bar chart or reference table. The visualization type options depend on the fields used to create the summary table.

Use the Maximize button to enlarge the card. Other cards on the page will be reduced to thumbnails. The card can be returned to its previous size using the Restore down button .

Use the Enable cross filters button to allow filters to be created on the card using selections on other cards. Cross filters can be removed using the Disable cross filters button .

Use the Flip card button to view the back of the card. The Card info tab provides information about the data on the card and the Export data tab allows users to export the data from the card.

Use the Card options button to access the following options:

• Appearance button —Change the background color, foreground color, border of the card, and map rotation, and add or remove the basemap layers and north arrow.
• Order button —Move the card forward or move the card backward relative to other cards on the page.
• Delete button —Remove the card from the page. If you did not intend to delete the card, you can retrieve it using the Undo button .

Resources

Use the following resources to learn more about tables:

• Create a table
• Create a reference table