Creating Calculations from Table Rows or Columns

Select one or more measure headers in a table and use them to create calculation rows or columns.

Context

Instead of adding a row or column to the table first and then creating a formula for the measure, you can select a measure header and pick a formula from the list. You can also select multiple headers and create a calculation from them.

Procedure

  1. In your table, do one of the following:

    • Using a single measure header.

      Right-click a measure header, select Add calculation, select one of the calculations, and then select whether you want a repeating dimension or a single dimension.

      • Repeating: adds a column or row based on each recurrence of the dimension member.

      • Single: adds only one column or row based on the combination of dimension members.

      Restriction

      Running functions (for example, Accumulative Sum, Moving Minimum Value, Moving Average, and so on) shouldn't be used for hierarchical data.

      Option Function

      Moving Minimum Value

      		 runningmin ( <value>)

      Moving Maximum Value

      		 runningmax ( <value>)

      Accumulative Sum

      		 runningsum ( <value>)

      Accumulative Sum of all Detailed Values that are not Zero, Null, or Error

      		 runningsum (<value>, true)

      Accumulative Sum of Rounded Values

      		 runningsum ( round (<value>))

      Accumulative Count of All Detailed Values

      		 runningcount <value>

      Accumulative Count of all Detailed Values that are not Zero, Null, or Error

      		 runningcount (<value>, true)

      Moving Average

      		 runningaverage (<value>)

      Moving Average that is not Zero, Null, or Error

      		 runningaverage ( <value>, true )

      Rank Number

      		 rank (<value>)

      Olympic Rank Number

      		 olympicrank (<value>)

      Comment

      		 comment (<value>)

    • Using multiple measure headers.

      1. Select two or more measure headers: select one header and then press Ctrl and select one or more measure headers.

      2. Right-click and then select Add calculation, select one of the calculations, and then select whether you want a repeating dimension or a single dimension.

        • Repeating: adds a column or row based on each recurrence of the dimension member.

        • Single: adds only one column or row based on the combination of dimension members.

        Calculation options:
        • Sum
        • Subtract
        • Multiply
        • Divide
        • Percentage Difference
        • Percentage Share
  2. To hide the reference (original) column or row, select the dimension header and then select Hide column (or Hide row).

    The calculation or comment column or row is still visible. To show the reference column, remove the Hidden filter from the table.

Results

A new row or column containing calculations is added to the table, and the formula is added to the builder panel as Story Calculations for the dimension.

When you select the header for the calculation row or column, the calculation formula is displayed in the formula bar. The calculation formula will also be displayed when you select (Edit calculation) for the story calculation in the builder panel.

Related Information