Create and use a summary table

Insights in ArcGIS Online
Insights in ArcGIS Enterprise
Insights desktop

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.

Regions in the United States and their corresponding average cost for colleges

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 Visualization type 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 Results 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:

DayField sumField 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 Card filter 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 Invert selection 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 Show selection 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 Visualization type 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 Maximize 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 Restore down.

Use the Enable cross filters button Enable cross filters 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 Disable cross filters.

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

Use the Card options button Card options to access the following options:

  • Appearance button Appearance—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 Order—Move the card forward or move the card backward relative to other cards on the page.
  • Delete button Delete—Remove the card from the page. If you did not intend to delete the card, you can retrieve it using the Undo button Undo.

Resources

Use the following resources to learn more about tables: