Adding Calculated Rows and Columns in a Table

You can add a calculated member to any dimension in a table by inserting a blank row or column next to an existing dimension member, and typing a formula.

Context

While you can still create calculated members based on the Account or Cross Calculations dimensions using the Calculation Editor, creating calculated rows and columns can provide more flexibility. For example, you don't really need to create a restricted measure to calculate the variance between an Actual and a Budget version.

You can create a calculated row or column based on the following:
  • Repeating: uses one dimension member and the calculation appears wherever that dimension member appears.

  • Single: uses multiple dimensions in the calculation and appears only once.

Note
If you have more than one dimension defined in your column or row, make sure the cell references refer to the inner most dimension.

Procedure

  1. To add a blank row or column, right-click the header of a dimension member and select Add column (or Add row) and then select either Repeating or Single.

    The calculated row or column is created as a new member of the same dimension and hierarchy level as the member that you selected.

  2. Optionally, type a name for the calculation in the header cell.
  3. To enter the formula, type the equals sign (=) and then begin typing the formula.
    • Add cell references by typing the coordinates of the cell (for example, B2), or by selecting the cell to reference.

      The cell must belong to the same table as the calculation.

    • References to cells in a calculated row or column are relative.

      For example, if you type =C2-B2 in a column header, the fifth row of this column will calculate C5-B5, the sixth row will calculate C6-B6, and so on.

    • Create absolute or fixed references to cells.

      For example, if you want to multiply each row by the value in C2, you type =$C$2*B2.

    • The following are the most common formulas that you can create.

      Formula Example
      Sum =B2+C2
      Subtract =B2-C2
      Multiply =B2*C2
      Divide =B2/C2
      Percentage difference =(B2-C2)/C2
      Percentage share =B2/C2
    • Apply the =comment() function to create a comment column.

      For example, you can add a reference to a column header (=comment(B2)) to create and display comments for the data cells in that column. (When you export the data to a CSV file, you can also export the comment column.) Double-click a cell or select it and start typing to work with comments.

      For more information about commenting on data cells, see Adding Comments to a Data Cell.

  4. To edit, format, or delete a calculated row or column, open the Builder panel and expand the list of calculations for the dimension that the row or column was added to.

    The calculated rows or columns are referred to as Story Calculations in the builder panel. When you select a story calculation and choose (Edit calculation), the calculated column or row is highlighted in the table and the formula is displayed in the formula bar.

Example: Creating a Variance column

To calculate the difference between a Budget and Actuals version, you can add a calculated column based on the Version dimension:
  1. Right-click the column header of the Budget version and select Add column.

  2. To name the new column, select the header, type Variance, and press Enter.
  3. Select the Variance cell again and type the formula. Use the cell coordinates of the Budget and Actuals column headers, or select each column to create references. For example, =C2-B2. Press Enter.
Add a calculated Variance column

The new column shows the difference between the Budget and Actual version for each account. You can also perform this calculation using restricted measures based on the Cross Calculations dimension.