Menu
Amazon QuickSight
User Guide

Working with Table Calculations

You can use table calculations to apply statistical functions to pivot table cells. Use the following sections to understand how to apply or remove a table calculation, the functions you can use in calculations, and the different ways you can apply a calculation to a pivot table.

Adding and Removing Table Calculations

Use the following procedures to add, modify, and remove table calculation on a pivot table.

Adding a Table Calculation

Use the following procedure to add a table calculation to a pivot table.

Note

Applying a table calculation changes the data type of the cell values if the data type doesn't make sense with the applied calculation. For example, if you apply the Rank function to a numeric field that you modified to use a currency data type, the cell values display as integers rather than currency. Similarly, if you apply the Percent difference function instead, the cell values display as percentages rather than currency. For more information about changing field data types in an analysis, see Changing a Field Data Type.

  1. Expand the Field wells pane by choosing the expand icon.

  2. Choose the field in the Values well that you want to apply a table calculation to, choose Add table calculation, and then choose the function to apply.

Changing How a Calculation Is Applied

Use the following procedure to change the way a table calculation is applied to a pivot table.

  1. Expand the Field wells pane by choosing the expand icon.

  2. Choose the field in the Values well that has the table calculation you want to change, choose Calculate as, and then choose the way you want the calculation applied.

Removing a Calculation

Use the following procedure to remove a table calculation from a pivot table.

  1. Expand the Field wells pane by choosing the expand icon.

  2. Choose the field in the Values well that you want to remove the table calculation from, and then choose Remove calculation.

Functions for Table Calculations

You can use the following functions in pivot table calculations.

Running Total

The running total function calculates the sum of a given cell value and the values of all cells prior to it. This is calculated as Cell1=Cell1, Cell2=Cell1+Cell2, Cell3=Cell1+Cell2+Cell3, etc. For example, given the following data:

Applying the running total function across the table rows gives you the following results:

Difference

The difference function calculates the difference between a cell value and value of the cell prior to it. This is calculated as Cell1=Cell1-null, Cell2=Cell2-Cell1, Cell3=Cell3-Cell2, etc. Because Cell1-null = null, the Cell1 value is always empty. For example, given the following data:

Applying the difference function across the table rows gives you the following results:

Percent Difference

The percent difference function calculates the difference between a cell value and the value of the cell prior to it, divided by the value of the cell prior to it. This is calculated as Cell1=(Cell1-null)/null, Cell2=(Cell2-Cell1)/Cell1, Cell3=(Cell3/Cell2)/Cell2, etc. Because (Cell1-null)/null = null, the Cell1 value is always empty. For example, given the following rows:

Applying the percent difference function across the table rows gives you the following results:

Percent of Total

The percent of total function calculates the percentage the given cell represents of the sum of all of the cells included in the calculation. This is calculated as Cell1=Cell1/(sum of all cells), Cell2=Cell2/(sum of all cells), etc. For example, given the following data:

Applying the percent of total function across the table rows gives you the following results:

Rank

The rank function calculates the rank of the cell value compared to the values of the other cells included in the calculation. Rank will always show the highest value equal to 1 and lowest value equal to the count of cells included in the calculation. If there are two or more cells with equal values, they receive the same rank but are considered to take up their own spots in the ranking, so the next highest value is pushed down in rank by the number of cells at the rank above it, minus one. For example, if you rank the values 5,3,3,4,3,2, their ranks would be 1,3,3,2,3,6.

Given the following data:

Applying the rank function across the table rows gives you the following results:

If you applied rank using table across down instead, so that the initial ranks are determined across the rows and then those ranks are in turn ranked down the columns, you get the following results. Note that the last column has two equal values sharing the top rank of 1, so the remaining value has a rank of 3.

Percentile

The percentile function calculates the percent of the values of the cells included in the calculation that are at or below the value for the given cell. This is calculated as the count of cell values that is less than (current cell value + (.05 * count of cell values equal to the current cell value)) / count of all cells. For example, given the following data:

Applying the percentile function across the table rows gives you the following results:

Ways to Apply Table Calculations

You can apply table calculations in the ways described following. Table calculations are applied to only one field at a time, so if you have a pivot table with multiple values, calculations are only applied to the cells representing the field you applied the calculation to.

Table Across

Using table across applies the calculation across the rows of the pivot table, regardless of any grouping. This is the default. For example, given the following pivot table:

Applying the running total function using table across gives you the following results, with row totals in the last column:

Table Down

Using table down applies the calculation down the columns of the pivot table, regardless of any grouping. For example, given the following pivot table:

Applying the running total function using table down gives you the following results, with column totals in the last row:

Table Across Down

Using table across down applies the calculation across the rows of the pivot table, and then takes the results and re-applies the calculation down the columns of the pivot table.

For the running total and difference functions, you get the same results whether you apply the function using table across down or table down across, because of the way those functions are calculated. For all other functions, the results will be different depending on whether you apply the function using table across down or table down across.

For example, given the following pivot table:

Applying the running total function using table across down or table down across gives you the following results, where totals are summed both down and across, with the grand total in the bottom right cell:

Applying the rank function using table across down, so that the initial ranks are determined across the table rows and then those ranks are in turn ranked down the columns, gives you the following results:

While applying the rank function using table down across, so that the initial ranks are determined down the table columns and then those ranks are in turn ranked across the rows, gives you the following results instead:

Table Down Across

Using table down across applies the calculation down the columns of the pivot table, then takes the results and re-applies the calculation across the rows of the pivot table.

For the running total and difference functions, you get the same results whether you apply the function using table down across or table across down, because of the way those functions are calculated. For all other functions, the results will be different depending on whether you apply the function using table down across or table across down.

For example, given the following pivot table:

Applying the running total function using table down across or table across down gives you the following results, where totals are summed both down and across, with the grand total in the bottom right cell:

Applying the rank function using table down across, so that the initial ranks are determined down the table columns and then those ranks are in turn ranked across the rows, gives you the following results:

While applying the rank function using table across down, so that the initial ranks are determined across the table rows and then those ranks are in turn ranked down the columns, gives you the following results instead:

Group Across

Using group across applies the calculation across the rows of the pivot table within group boundaries, as determined by the second level of grouping applied to the columns. For example, if you group by state and then by city, grouping is applied at the state level. If you group by region, state, and city, grouping is again applied at the state level. When there is no grouping, group across returns the same results as table across.

For example, given the following pivot table where columns are grouped by service category and then by consumption channel:

Applying the running total function using group across gives you the following results, where the function is applied across the rows, bounded by the columns for each service category group. The Mobile columns display the total for both consumption channels for the given service category, for the customer region and year represented by the given row. For example, the highlighted cell represents the total for the AP region for 2012, for all consumption channels in the Billing service category.

Group Down

Using group down applies the calculation down the columns of the pivot table within group boundaries, as determined by the second level of grouping applied to the rows. For example, if you group by state and then by city, grouping is applied at the state level. If you group by region, state, and city, grouping is again applied at the state level. When there is no grouping, group down returns the same results as table down.

For example, given the following pivot table where rows are grouped by region and then by year:

Applying the running total function using group down gives you the following results, where the function is applied down the columns, bounded by the rows for each region group. The 2014 rows display the total for all years for the given region, for the service category and consumption channel represented by the given column. For example, the highlighted cell represents the total for the Billing service category for the Mobile consumption channel, for all years in the AP region.

Group Across Down

Using group across down applies the calculation across the rows within group boundaries, as determined by the second level of grouping applied to the columns. Then the function takes the results and re-applies the calculation down the columns of the pivot table, within group boundaries as determined by the second level of grouping applied to the rows. For example, if you group a row or column by state and then by city, grouping is applied at the state level. If you group by region, state, and city, grouping is again applied at the state level. When there is no grouping, group across down returns the same results as table across down.

For the running total and difference functions, you get the same results whether you apply the function using group across down or group down across, because of the way those functions are calculated. For all other functions, the results will be different depending on whether you apply the function using group across down or group down across.

For example, given the following pivot table where columns are grouped by service category and then by consumption channel, and rows are grouped by region and then by year.

Applying the running total function using group across down or group down across gives you the following results, where totals are summed both down and across within the group boundaries—in this case, service category for the columns and customer region for the rows. The grand total appears in the bottom right cell for the group.

Applying the rank function using group across down gives you the following results, where the function is first applied across the rows bounded by each service category group. The function is then applied again to the results of that first calculation, this time applied down the columns bounded by each region group.

Applying the rank function using group down across gives you the following results instead, where the function is first applied down the columns bounded by each region group. The function is then applied again to the results of that first calculation, this time applied across the rows bounded by each service category group.

Group Down Across

Using group down across applies the calculation down the columns within group boundaries, as determined by the second level of grouping applied to the rows, and then takes the results and re-applies the calculation across the rows of the pivot table, again within group boundaries as determined by the second level of grouping applied to the columns. For example, if you group a row or column by state and then by city, grouping is applied at the state level. If you group by region, state, and city, grouping is again applied at the state level. When there is no grouping, group down across returns the same results as table down across.

For the running total and difference functions, you get the same results whether you apply the function using group down across or group across down, because of the way those functions are calculated. For all other functions, the results will be different depending on whether you apply the function using group down across or group across down.

For example, given the following pivot table where columns are grouped by service category and then by consumption channel, and rows are grouped by region and then by year:

Applying the running total function using group down across or group across down gives you the following results, where totals are summed both down and across within the group boundaries (in this case service category for the columns and customer region for the rows), with the grand total in the bottom right cell for the group:

Applying the rank function using group down across gives you the following results, where the function is first applied down the columns bounded by each region group. The function is then applied again to the results of that first calculation, this time applied across the rows bounded by each service category group:

Applying the rank function using group across down gives you the following results instead, where the function is first applied across the rows bounded by each service category group. The function is then applied again to the results of that first calculation, this time applied down the columns bounded by each region group: