You can use the Calculation Wizard to create new calculation columns that compare two existing columns, and also to create metrics in bulk.
It has a built-in mechanism to handle divide-by-zero and null cases, as well as other difficult situations. The Calculation Wizard provides an automated way to calculate the sales by quarter, the percentage of revenue, minimum and maximum values, and so on.
In the Calculation Wizard, the New Calculation page select from the following options:
Change (CurrentX - ComparisonX). Subtract the value of the comparison column from the source column.
For example, for the Change calculation, you can choose whether to return NULL
or some other value when the comparison column is NULL
.
Percent Change (100.0 * (CurrentX - ComparisonX) / ComparisonX). Subtract the value of the comparison column from the source column and express as a percentage.
Index (1.0 * CurrentX / ComparisonX). Divide the source column by the comparison column.
Percent (100.0 * (CurrentX / ComparisonX)). Divide the source column by the comparison column and express as a percentage.
The image shows the Calculation Wizard, the New Calculation page.