Insert Formula columns

In Plan, you can insert calculated columns or measures directly into your planning sheets. These can be formatted, rearranged, and reused in subsequent calculations. These measures and columns are created directly on the visual, without modifying the underlying data model.

The Excel-like formula engine supports over 50 functions, including logical, boolean, and mathematical functions. The formula editor provides capabilities such as syntax assistance, examples, autocomplete, and multiline editing to help you create and troubleshoot formulas efficiently.

In this article, you learn how to insert formula measures and columns and use the formula editor to build the required calculations.

For more information, see Formula syntax for a detailed list of supported functions, operators, and identifiers.

Insert a formula measure

In this example, consider sales data for two years (2024 and 2025) by quarter. You'll create a measure to calculate percentage variance using the formula: (2025 Actuals - 2024 Actuals) / 2024 Actuals.

To insert a formula measure:

1. Go to Planning > Insert Column and select Formula.

1. In the Formula Measure side panel:

   • Enter a Title.

   • In Insert as, select Visual Measure (default).

   You can also choose Visual Column to add a column at the end of the table, outside of the column hierarchy.

2. Enter the formula in the editor and select Create.

1. To convert the values of the Variance% measure to a percentage format, select the measure and then select the % icon on the Planning tab.

When the Variance% measure is selected, the formula is displayed in the formula bar.

Working with the formula editor

• When you place the cursor in the editor, a context assistant appears with Functions and References.

• As you type, suggestions are filtered automatically.

• To reference measures or visual columns, use the References tab.

• To insert functions such as SUM, MIN, MAX, and AVERAGE, use the Functions tab.

• You can also select columns directly from the visual after placing the cursor in the formula editor to insert references.

After creating the measure, you can format it (for example, as a percentage or currency) from the Planning tab.

Configure formula measure properties

Configure the properties for formula measures in the same way as other data input measures. For more information, see configure data input column properties.

To edit the properties or to hide or delete a formula column, use the Manage measures menu.

Note

If the report does not contain a column hierarchy, the Insert as Measure option behaves like inserting a visual column by default, even if Visual Measure is selected.

Handle calculation errors

You may encounter errors such as division by zero. You can handle these in the following ways:

Use appearance settings

• Go to Format > Appearance > Numbers.

• Enable Suppress calculation errors.

• Provide a custom value (for example, 0 or N.A.)

Use functions in formulas

• Use IFNA, IF, or nested IF statements to handle conditions explicitly and replace error values.

Aggregation for formula measures

By default, Row aggregation is set to Formula and Column aggregation is set to Sum.

You can modify aggregation settings from the Manage aggregation interface. For more information, see manage aggregation.

Note

If you select Weighted average as the row aggregation, the column aggregation is also set to weighted average and cannot be changed.