Use variance columns to calculate differences between report columns. Variance columns support predefined formulas and custom calculations.
In this article, we will cover:
Variance Column Behavior
Variance columns compare values between report columns and display the calculated result directly in the report.
You can:
Use predefined variance formulas
Create custom variance calculations
Compare multiple columns
Apply sign reversal behavior
Display numeric or percentage results
Benefits of Variance Columns
Variance columns provide the following benefits:
| Benefit | Description |
|---|---|
| Flexibility | Compare any numeric columns, such as Actual versus Budget or Current Year versus Previous Year. |
| Transparency | Display calculations directly in the report without requiring external spreadsheets. |
| Speed | Create calculations without developer involvement. |
| Decision Support | Identify performance gaps and trends quickly. |
Creating a Custom Variance Column
Open the Report Editor.
Select the Columns tab.
-
Select Create.
In Type, select Variance.
In Variance Type, select Custom.
Enter a column name.
Select Save.
Editing a Custom Variance Column
Open the Columns tab.
Select the custom variance column.
-
Select Edit.
Configuring a Custom Variance Formula
You can build formulas by combining multiple columns and operations.
Adding Formula Rows
In the variance editor, select +.
Add additional formula rows as needed.
Selecting Columns
In each formula row, open the Column drop-down list.
-
Select the column to include in the calculation.
Selecting Operations
You can use the following operations in a custom variance formula:
| Operation | Description |
|---|---|
| + | Addition |
| - | Subtraction |
| * | Multiplication |
| / | Division |
Applying Multipliers
In the Multiply With field, enter a multiplier value.
Repeat for additional formula rows as needed.
Selecting a Specific Member
Instead of using an entire column, you can calculate against a specific member.
Open the member selector.
Select the member to include in the formula.
Parameter Value Options
Each report value contains:
A raw value
A visible value
These values can differ because of sign formatting or reversal behavior.
You can select which value type the variance formula uses for calculations.
| Parameter Value | Description |
|---|---|
| Raw Values | Uses the underlying stored value. |
| Visible values | Uses the displayed report value after formatting or sign adjustments. |
Sign Reversal Options
You can apply sign reversal behavior to the final variance calculation result.
The following options are available:
| Option | Behavior |
|---|---|
| Row based | Multiplies the calculated value by the row sign. |
| Row based reversed | Multiplies the calculated value by the row sign and then multiplies by -1. |
| As shown | Displays the calculated value without additional sign multiplication. |
| Reversed | Multiplies the calculated value by -1. |
Variance Calculation Logic
Variance calculations compare values between selected columns.
Depending on configuration, the system calculates:
Absolute variance
Percentage variance
Example Formulas:
(Column A - Column B)
((Column A - Column B) / Column B) * 100
Example: Budget Versus Actual Sales
The following example compares Budget Sales and Actual Sales values.
| Column | Value |
|---|---|
| Budget Sales | 1,200,000 |
| Actual Sales | 1,050,000 |
Variance Formula
Actual Sales - Budget Sales
Result
| Calculation Type | Result |
|---|---|
| Absolute Variance | -150,000 |
| Percentage Variance | -12.5% |
Managers can use this variance to identify underperforming product categories or business units.
Interaction with Other Features
Variance columns integrate with the following features:
| Feature | Behavior |
|---|---|
| Sum Columns | Variance values can be aggregated into totals. |
| Hierarchical Rollups | Variance values roll up according to report hierarchy levels. |
| Sign Reversal | Sign reversal applies before the variance calculation completes. |
Follow these recommendations when using variance columns:
Use consistent variance formatting across reports.
Clearly indicate whether values represent absolute or percentage variance.
Use variance columns for planned versus actual, period-over-period, or KPI comparisons.
Review percentage calculations carefully when source values may contain zero values.
When percentage variance calculations divide by zero, the system displays N/A.