xpna

Appearance

Building Calculation Formulas ​

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