Calculating variance percentage

You can find the percentage difference between two rows or columns in IBM® Cognos® Insight by calculating the variance percentage. The variance percentage calculation is the difference between two numbers, divided by the first number, then multiplied by 100.

1. Select the rows or columns that you want to use.
2. Right-click the rows or columns and click Calculate, and then click Custom.
   The Calculation window appears with a default expression in the Expression field.
3. In the Expression field, create the following expression: ['first_data_item'] - ['second_data_item'] / ['first_data_item'] * 100.
   Tip: When you type [' into the Expression field, a list appears with the dimensions in your cube. You can select the dimension from this list instead of typing it into the Expression field. The same list will appear any time you type ['.
   For example, type the following expression: (['actualrevenue'] - ['forecastrevenue']) / ['forecastrevenue'] * 100.
4. Under Calculation order, ensure that Summarize first, then apply the calculation is selected. This option means that Cognos Insight performs the calculation on the total values. Choosing Apply the calculation first, then summarize applies the calculation to each row or column first, and then sums the calculation results.
5. In the Name field, type Variance Percentage.
6. Click OK.
7. To format the new calculated data to appear with a percentage symbol, right-click a calculated cell and click Format Measure measure_name.
8. In the list of formats, click Number.
9. On the Advanced tab, type % in the positive and negative Suffix fields.