Adding Table Calculations in Pivot Tables

You can use table calculations to apply statistical functions to pivot table cells that contain numeric values.

The data type of the cell value automatically changes according to the calculation. For example, if you apply the Rank function to a currency field, the values display as integers rather than currency. Similarly, if you apply the Percent difference function, the cell values display as percentages.

You cannot run Total, Difference, and Percent Difference table calculations on a pivot table that is sorted by a measure. You must remove the sorting option and then add the calculation.

To add table calculations in a pivot table:
  1. Click Dashboards tab, the Dashboards tab.
  2. Click Dashboard Designer, in the bottom-left corner.
  3. On the Analyses dialog, click Options button, the Options button, next to an analysis and select Edit.
  4. On the analysis page, select a pivot table.
  5. In the Visuals panel, click Options button, the Options button, next to a field from the Values field well.
  6. Select Add table calculation and choose a function from the list:
    • Running total – Calculates the sum of a cell value and the values of all the cells before it. This sum is calculated as Cell1=Cell1, Cell2=Cell1+Cell2, Cell3=Cell1+Cell2+Cell3, and so on.
    • Difference – Calculates the difference between a cell value and the value of the cell before 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.
    • Percentage difference – Calculates the percent difference between a cell value and the value of the cell before it, divided by the value of the cell before 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.
    • Percent of total – Calculates the percentage that a cell represents out of the sum of all 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.
    • Rank – Calculates the rank of a cell value compared to the values of the other cells included in the calculation. Rank always shows the highest value equal to 1 and the 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 they each occupy a spot in the ranking. As a result, the next highest value moves down in rank by the number of cells at the rank above it, minus 1. For example, if you rank the values 5,3,3,4,3,2, their ranks are 1,3,3,2,3,6.

    • Percentile – Calculates the percent of the values of the cells included in the calculation that are at or below the value for a given cell. This percent is calculated as percentile rank(x) = 100 * B / N, where B = number of scores below x and N = number of scores.
  7. To change how the calculation is applied, click Options button, the Options button, next to a field with a calculation, then select Calculate as and choose an option from the list:
    • Table across – Applies the calculation across the rows, regardless of any grouping.
    • Table down – Applies the calculation down the columns, regardless of any grouping.
    • Table across down – Applies the calculation across the rows, and then takes the results and reapplies the calculation down the columns.
    • Table down across – Applies the calculation down the columns, and then takes the results and reapplies the calculation across the rows.
    • Group across – Applies the calculation across the rows 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.

    • Group down – Applies the calculation down the columns 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.

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

    • Group down across – Applies the calculation down the columns within group boundaries, as determined by the second level of grouping applied to the rows. Then the function takes the results and reapplies the calculation across the rows 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.

You can remove a table calculation, by clicking Options button, the Options button, next to a field with a calculation and selecting Remove calculation.
Parent topic: Using Pivot Tables