Add calculated rows and columns

Insert a calculation to make your Exploration View more meaningful by deriving additional information from the data source. For example, you create an invoice, and you want to see the total sales amount for each product ordered.

About this task

In addition to simple arithmetic calculations, you can perform the following calculations:

% Of
Calculates the value of a selected member as a percentage of another member, for example, fourth quarter as a percentage of the whole year or actual as a percentage of target.
% Change
Calculates the change in value of a selected member as a percentage, for example, growth from year to year or variance from target.
% Of Base
This calculation is available only if you select two members from different hierarchies, one on rows and the other on columns, for example, each region's contribution (on rows) to a yearly total (on columns).
% Of Parent
This calculation is available only for TM1 data. The % of Parent calculation is available only if you select two members from different dimensions, one on rows and the other on columns. % Of Parent calculates the value of a selected member as a percentage of its parent, for example, January sales as a percentage of sales for the whole year.

After you insert a calculation into an Exploration View, the rows and columns are separated into two distinct blocks of items before or after the inserted row or column. If you want to use the Expand level / Collapse level feature, you must do so for each block separately.

When calculations in the rows and columns of an Exploration View intersect, calculations are performed in the following order:
  • Addition or subtraction
  • Multiplication or division

If both calculations have the same precedence, for example, if they are both functions, then the row calculation takes precedence.

For information about adding Microsoft Excel calculations, see Create a calculated column or row from scratch.

Procedure

  1. Right-click the columns or row headers that you want to use in the calculation.
  2. Click IBM® Planning Analytics > Insert calculation and select the calculation that you want to perform.
    Note: Calculations that are not applicable to the items you selected are grayed out.

Results

The calculated row or columns appears in the Exploration View. The calculated item is named using a combination of the member names that contribute to the calculation. You can rename the calculated column or row. You can also move the calculated column or row.
Note: To remove a calculation, delete the row or column that contains the calculation from the spreadsheet.