# Using table calculations in pivot tables


You can use table calculations to apply statistical functions to pivot table cells that contain measures (numeric values). Use the following sections to understand which functions you can use in calculations, and how to apply or remove them.

The data type of the cell value automatically changes to work for your calculation. For example, say that you apply the **Rank** function to a currency data type. The values display as integers rather than currency, because rank isn't measured as currency. Similarly, if you apply the **Percent difference** function instead, the cell values display as percentages. 

**Topics**
+ [

# Adding and deleting pivot table calculations
](adding-a-calculation.md)
+ [

# Functions for pivot table calculations
](supported-functions.md)
+ [

# Ways to apply pivot table calculations
](supported-applications.md)

# Adding and deleting pivot table calculations


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

**Topics**
+ [

# Adding a pivot table calculation
](add-a-calculation.md)
+ [

# Changing how a calculation is applied
](change-how-a-calculation-is-applied.md)
+ [

# Removing a calculation
](remove-a-calculation.md)

# Adding a pivot table calculation


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

**To add a table calculation to a pivot table**

1. Expand the **Field wells** pane by choosing the field wells near the bottom of the visual.

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

**Note**  
You can't run Total, Difference, and Percent Difference table calculations when a pivot table is being sorted by a measure. To use these table calculations, remove the sort from the pivot table.

# Changing how a calculation is applied


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

**To change the way a table calculation is applied to a pivot table**

1. Expand the **Field wells** pane by choosing field wells at the top of the visual.

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

# Removing a calculation


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

**To remove a table calculation from a pivot table**

1. Expand the **Field wells** pane by choosing the field wells near the bottom of the visual.

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

# Functions for pivot table calculations


You can use the following functions in pivot table calculations.

**Topics**
+ [

## Running total
](#running-total)
+ [

## Difference
](#difference)
+ [

## Percentage difference
](#percent-difference)
+ [

## Percent of total
](#percent-of-total)
+ [

## Rank
](#rank)
+ [

## Percentile
](#percentile)

You can apply functions listed to the following data:

![\[alt text not found\]](http://docs.amazonaws.cn/en_us/quick/latest/userguide/images/running-total1.png)


![\[alt text not found\]](http://docs.amazonaws.cn/en_us/quick/latest/userguide/images/running-total1.png)


## Running total


The **Running total** function calculates the sum of a given cell value and the values of all cells prior to it. This sum is calculated as `Cell1=Cell1, Cell2=Cell1+Cell2, Cell3=Cell1+Cell2+Cell3`, and so on. 

Applying the **Running total** function across the table rows, using **Table across** for **Calculate as**, gives you the following results.

![\[alt text not found\]](http://docs.amazonaws.cn/en_us/quick/latest/userguide/images/running-total2.png)


## Difference


The **Difference** function calculates the difference between a cell value and value of the cell prior to it. This difference is calculated as `Cell1=Cell1-null, Cell2=Cell2-Cell1, Cell3=Cell3-Cell2,` and so on. Because `Cell1-null = null`, the Cell1 value is always empty.

Applying the **Difference** function across the table rows, using **Table across** for **Calculate as**, gives you the following results.

![\[alt text not found\]](http://docs.amazonaws.cn/en_us/quick/latest/userguide/images/difference.png)


## Percentage difference


The **Percentage Difference** function calculates the percent 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 value is calculated as `Cell1=(Cell1-null)/null, Cell2=(Cell2-Cell1)/Cell1, Cell3=(Cell3-Cell2)/Cell2,` and so on. Because `(Cell1-null)/null = null`, the Cell1 value is always empty.

Applying the **Percentage Difference** function across the table rows, using **Table across** for **Calculate as**, gives you the following results.

![\[alt text not found\]](http://docs.amazonaws.cn/en_us/quick/latest/userguide/images/percentage-difference.png)


## 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 percentage is calculated as `Cell1=Cell1/(sum of all cells), Cell2=Cell2/(sum of all cells),` and so on. 

Applying the **Percent of Total** function across the table rows, using **Table across** for **Calculate as**, gives you the following results.

![\[alt text not found\]](http://docs.amazonaws.cn/en_us/quick/latest/userguide/images/percent-of-total.png)


## Rank


The **Rank** function calculates the rank of the cell value compared to the values of the other cells included in the calculation. Rank always shows 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. Thus, 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 are 1,3,3,2,3,6. 

For example, suppose that you have the following data.

![\[alt text not found\]](http://docs.amazonaws.cn/en_us/quick/latest/userguide/images/rank.png)


Applying the **Rank** function across the table rows, using **Table across** for **Calculate as**, gives you the following results.

![\[alt text not found\]](http://docs.amazonaws.cn/en_us/quick/latest/userguide/images/rank2.png)


## 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 percent is calculated as follows. 

```
percentile rank(x) = 100 * B / N

Where:
   B = number of scores below x
   N = number of scores
```

Applying the **Percentile** function across the table rows, using **Table across** for **Calculate as**, gives you the following results.

![\[alt text not found\]](http://docs.amazonaws.cn/en_us/quick/latest/userguide/images/percentile.png)


# Ways to apply pivot table calculations


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

**Topics**
+ [

## Table across
](#table-across)
+ [

## Table down
](#table-down)
+ [

## Table across down
](#table-across-down)
+ [

## Table down across
](#table-down-across)
+ [

## Group across
](#group-across)
+ [

## Group down
](#group-down)
+ [

## Group across down
](#group-across-down)
+ [

## Group down across
](#group-down-across)

## Table across


Using **Table across** applies the calculation across the rows of the pivot table, regardless of any grouping. This application is the default. For example, take the following pivot table.

![\[alt text not found\]](http://docs.amazonaws.cn/en_us/quick/latest/userguide/images/sample-pivot.png)


Applying the **Running total** function using **Table across** gives you the following results, with row totals in the last column.

![\[alt text not found\]](http://docs.amazonaws.cn/en_us/quick/latest/userguide/images/table-across.png)


## Table down


Using **Table down** applies the calculation down the columns of the pivot table, regardless of any grouping.

Applying the **Running total** function using **Table down** gives you the following results, with column totals in the last row.

![\[alt text not found\]](http://docs.amazonaws.cn/en_us/quick/latest/userguide/images/table-down.png)


## Table across down


Using **Table across down** applies the calculation across the rows of the pivot table, and then takes the results and reapplies the calculation down the columns of the pivot table.

Applying the **Running total** function using **Table across down** gives you the following results. In this case, totals are summed both down and across, with the grand total in the lower-right cell.

![\[alt text not found\]](http://docs.amazonaws.cn/en_us/quick/latest/userguide/images/running-total-across-down.png)


In this case, suppose that you apply the **Rank** function using **Table across down**. Doing so means that the initial ranks are determined across the table rows and then those ranks are in turn ranked down the columns. This approach gives you the following results.

![\[alt text not found\]](http://docs.amazonaws.cn/en_us/quick/latest/userguide/images/rank-table-across-down.png)


## Table down across


Using **Table down across** applies the calculation down the columns of the pivot table. It then takes the results and reapplies the calculation across the rows of the pivot table. 

You can apply the **Running total** function using **Table down across** to get the following results. In this case, totals are summed both down and across, with the grand total in the lower-right cell.

![\[alt text not found\]](http://docs.amazonaws.cn/en_us/quick/latest/userguide/images/running-total-down-across.png)


You can apply the **Rank** function using **Table down across** to get the following results. In this case, the initial ranks are determined down the table columns. Then those ranks are in turn ranked across the rows.

![\[alt text not found\]](http://docs.amazonaws.cn/en_us/quick/latest/userguide/images/rank-table-down-across.png)


## 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 field-2 and then by field-1, grouping is applied at the field-2 level. If you group by field-3, field-2, and field-1, grouping is again applied at the field-2 level. When there is no grouping, **Group across** returns the same results as **Table across**. 

For example, take the following pivot table where columns are grouped by `Service Line` and then by `Consumption Channel`.

![\[alt text not found\]](http://docs.amazonaws.cn/en_us/quick/latest/userguide/images/sample-pivot.png)


You can apply the **Running total** function using **Group across** to get the following results. In this case, 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 Channel` values for the given `Service Line`, for the `Customer Region` and `Date` (year) represented by the given row. For example, the highlighted cell represents the total for the `APAC` region for `2012`, for all `Consumption Channel` values in the `Service Line` named `Billing`.

![\[alt text not found\]](http://docs.amazonaws.cn/en_us/quick/latest/userguide/images/group-across.png)


## 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 field-2 and then by field-1, grouping is applied at the field-2 level. If you group by field-3, field-2, and field-1, grouping is again applied at the field-2 level. When there is no grouping, **Group down** returns the same results as **Table down**.

For example, take the following pivot table where rows are grouped by `Customer Region` and then by `Date` (year).

![\[alt text not found\]](http://docs.amazonaws.cn/en_us/quick/latest/userguide/images/sample-pivot.png)


You can apply the **Running total** function using **Group down** to get the following results. In this case, the function is applied down the columns, bounded by the rows for each `Customer Region` group. The `2014` rows display the total for all years for the given `Customer Region`, for the `Service Line` and `Consumption Channel` represented by the given column. For example, the highlighted cell represents the total the `APAC` region, for the `Billing` service for the `Mobile` channel, for all the `Date` values (years) that display in the report.

![\[alt text not found\]](http://docs.amazonaws.cn/en_us/quick/latest/userguide/images/group-down.png)


## 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 reapplies the calculation down the columns of the pivot table. It does so 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 field-2 and then by field-1, grouping is applied at the field-2 level. If you group by field-3, field-2, and field-1, grouping is again applied at the field-2 level. When there is no grouping, **Group across down** returns the same results as **Table across down**.

For example, take the following pivot table where columns are grouped by `Service Line` and then by `Consumption Channel`. Rows are grouped by `Customer Region` and then by `Date` (year).

![\[alt text not found\]](http://docs.amazonaws.cn/en_us/quick/latest/userguide/images/sample-pivot.png)


You can apply the **Running total** function using **Group across down** to get the following results. In this case, totals are summed both down and across within the group boundaries. Here, these boundaries are `Service Line` for the columns and `Customer Region` for the rows. The grand total appears in the lower-right cell for the group.

![\[alt text not found\]](http://docs.amazonaws.cn/en_us/quick/latest/userguide/images/running-total-group-across-down.png)


You can apply the **Rank** function using **Group across down** to get the following results. In this case, the function is first applied across the rows bounded by each `Service Line` group. The function is then applied again to the results of that first calculation, this time applied down the columns bounded by each `Customer Region` group.

![\[alt text not found\]](http://docs.amazonaws.cn/en_us/quick/latest/userguide/images/rank-group-across-down.png)


## Group down across


Using **Group down across** applies a calculation down the columns within group boundaries, as determined by the second level of grouping applied to the rows. Then Amazon Quick takes the results and reapplies the calculation across the rows of the pivot table. Again, it reapplies the calculation 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 field-2 and then by field-1, grouping is applied at the field-2 level. If you group by field-3, field-2, and field-1, grouping is again applied at the field-2 level. When there is no grouping, **Group down across** returns the same results as **Table down across**.

For example, take the following pivot table. Columns are grouped by `Service Line` and then by `Consumption Channel`. Rows are grouped by `Customer Region` and then by `Date` (year).

![\[alt text not found\]](http://docs.amazonaws.cn/en_us/quick/latest/userguide/images/sample-pivot.png)


You can apply the **Running total** function using **Group down across** to get the following results. In this case, totals are summed both down and across within the group boundaries. In this case, these are `Service Category` for the columns and `Customer Region` for the rows. The grand total is in the lower-right cell for the group.

![\[alt text not found\]](http://docs.amazonaws.cn/en_us/quick/latest/userguide/images/running-total-group-across-down.png)


You can apply the **Rank** function using **Group down across** to get the following results. In this case, the function is first applied down the columns bounded by each `Customer 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 Line` group.

![\[alt text not found\]](http://docs.amazonaws.cn/en_us/quick/latest/userguide/images/rank-group-down-across.png)