xpna

Appearance

Building Calculation Formulas ​

Notes:

Web is currently in feature preview and as such documentation for this feature is still work in progress

The formula editor is where the calculation logic is defined. This page explains how to use the formula editor to build calculations using members, operators, and parentheses.

Formula Editor Overview ​

The formula editor is located in the calculation detail panel when viewing or editing a calculation. It provides an intuitive interface for building formulas by combining:

  • Members - Data points from hierarchies (e.g., specific accounts, entities, periods)
  • Arithmetic Operators - Mathematical operations (+, -, *, /)
  • Parentheses - Grouping operations to control order of evaluation

Building a Formula ​

Adding Members ​

Members are the data points from the workspace hierarchies that are used in calculations.

To add a member to the formula:

  • Start typing the name of the hierarchy the member belongs to (e.g., "Accounts", "Entity", "Period").
  • A dropdown list appears showing matching hierarchies.
  • Use the mouse or arrow keys to select the desired hierarchy.
  • Click the item or press Enter, Tab, or . to confirm the hierarchy selection.
  • Continue typing to search for the specific member within that hierarchy.
  • Select the desired member from the dropdown list.
  • Click the item or press Enter, Tab, or . to confirm the member selection.

Example:

To add the "Revenue" account from the Accounts hierarchy:

  • Type "Acc" to filter to the Accounts hierarchy.
  • Press Enter when "Accounts" is selected.
  • Type "Rev" to filter to revenue-related accounts.
  • Select "Revenue" from the list.
  • Press Enter to add it to the formula.

Adding Operators ​

The following arithmetic operators are supported:

  • + (Addition) - Adds two values together
  • - (Subtraction) - Subtracts the second value from the first
  • ***** (Multiplication) - Multiplies two values together
  • / (Division) - Divides the first value by the second

Using Parentheses ​

Parentheses are used to group operations and control the order of evaluation, following standard mathematical conventions.

Example:

([Revenue] - [Cost of Sales]) / [Revenue]

In this example, parentheses ensure that the subtraction is performed before the division.

Formula Examples ​

Simple Ratio ​

Calculate gross margin percentage:

[Gross Profit] / [Revenue]

This divides the Gross Profit member by the Revenue member.

Variance Calculation ​

Calculate the difference between actuals and budget:

[Actuals] - [Budget]

This subtracts the Budget member from the Actuals member.

Percentage Variance ​

Calculate the percentage variance between actuals and budget:

([Actuals] - [Budget]) / [Budget]

This calculates how much actuals differs from budget as a percentage of budget.

Complex Calculation ​

Calculate EBITDA margin with multiple operations:

([Revenue] - [COGS] - [Operating Expenses]) / [Revenue]

This demonstrates combining multiple subtractions within parentheses before performing the division.

Number Formatting ​

After building the formula, a number format can be applied to control how the calculation result is displayed.

Common number formats include:

  • 0 - Integer with no decimal places
  • 0.00 - Number with two decimal places
  • 0% - Percentage with no decimal places
  • 0.0% - Percentage with one decimal place
  • #,##0 - Integer with thousand separators

For detailed information about number formatting options, see the Number Formatting documentation.

Example:

For a gross margin percentage calculation, use the format 0.0% to display results like "35.2%".

Best Practices ​

Formula Design ​

  • Use Meaningful Members - Select members that clearly represent the data needed for the calculation.
  • Document Complex Logic - For calculations with complex business logic, maintain external documentation explaining the reasoning.

Order of Operations ​

  • Follow Mathematical Conventions - Calculations follow standard order of operations (multiplication and division before addition and subtraction).
  • Use Parentheses for Clarity - Even when not strictly required, parentheses can make formulas more readable.
  • Group Related Operations - Use parentheses to group related operations together.

Common Patterns ​

  • Ratios - Divide one member by another (e.g., [A] / [B]).
  • Margins - Calculate profit margins using ([Revenue] - [Cost]) / [Revenue].
  • Variances - Compare actuals to plan using [Actuals] - [Plan].
  • Growth Rates - Calculate period-over-period growth using ([Current Period] - [Prior Period]) / [Prior Period].

Troubleshooting ​

Member Not Found ​

If a member cannot be found when building a formula:

  • Verify the member exists in the workspace.
  • Check that the correct hierarchy is selected.
  • Try searching with a different part of the member name.
  • Ensure the member has not been deleted or renamed.

Division by Zero ​

When building formulas that include division:

  • Be aware that dividing by zero or members with zero values will produce errors or undefined results.
  • Consider the data that will be used with the calculation.
  • In some cases, conditional logic may be needed (handled outside the calculation).

Copyright © 2025 xpna