Calculation Mastery: How to Write Formulas That Scale

Introduction

Building something that works is not enough in Pigment. The goal is to build it right and to optimise for performance from the first design decision to the last. This guide consolidates best practices from the Pigment Modeling Palette into a single reference, updated with practical examples and annotated with the reasoning behind each recommendation. A performant Pigment model is fast, scalable and maintainable: a lean engine that delivers results quickly and gets used daily.

The series covers every factor that affects performance: core dimensional architecture and sparsity; scoping and formula efficiency; refactoring, snapshots, transaction lists and import design; size estimation, version management, testing and user adoption. Each part includes concrete Workforce Planning and FP&A examples, comparisons of inefficient versus optimised formulas with estimated performance impacts, and a consolidated best practices versus pitfalls reference table in Part 4.

Who this guide is for. Expert Pigment modelers, solution architects and EPM professionals who want a definitive reference for building applications that remain fast as they scale, survive organisational change and earn the daily trust of the people who use them.

• Part 1 · Architectural Foundations: Dimensions, Sparsity and the Discipline of Scope
• Part 2 · Calculation Mastery: How to Write Optimised Formulas That Scale
• Part 3 · Lifecycle Performance: Views, Snapshots, Testing and Model Hygiene
• Part 4 · Performance in Action: Workforce and FP&A Patterns with Practical Reference

Calculation Mastery: How to Write Formulas That Scale

What This Part Covers

Part 2 covers the formula-writing practices that determine execution efficiency. The key decisions covered here are:

• When and how to reduce data scope early in a formula to avoid expensive intermediate expansions.
• How Pigment's dimensional modifiers transform metric dimensionality at each step, and why that must be tracked explicitly.
• How to aggregate from Transaction Lists correctly using the staging metric pattern.
• How to structure calculation chains for parallelism and maintainability.
• How to use the Formula Playground as a pre-commit validation tool before any metric is created.

1. Formula Efficiency and Execution Order: Scope First, Filter Early

The Reduce-First Principle

Compute only what you need, only where you need it. Use an IF condition or a [FILTER: ...] at the beginning of the expression, rather than computing a large result and filtering it afterward. The performance gain comes from narrowing the scope of data that subsequent operations have to process. The earlier in the formula chain the data is reduced, the less work each downstream step performs.

Example: Bonus Calculation

Anti-pattern: computes across all employees, then filters:

Bonus = (Salary * Bonus%)[FILTER: Department = "Sales"]

Optimised: applies the condition first, so non-Sales employees produce blank immediately:

Bonus = IF(Department = "Sales", Salary * Bonus%)

If Sales represents 20% of employees, the optimised formula processes roughly 5x fewer cells by skipping the 80% that will never produce a result.

Filter Before You Aggregate

Apply any filters or joins before heavy aggregations or expansions. If you are going to [BY SUM: Cost Center] on a transaction metric, pre-limit the data with a [FILTER: TransactionType = "Relevant"] first. Aggregating a million records only to discard half is always slower than filtering to half a million records and then aggregating.

Understand Execution Order

A[ADD: X][FILTER: condition] performs the ADD first (creating values across all X) and then filters. IF(condition, A[ADD: X]) enforces the condition first. The difference is significant: Metric[ADD: Region][FILTER: Region."NA"] materialises the metric across all regions then filters to North America, producing and immediately discarding data for every other region. Structure formulas so the data set never grows larger than the final output requires.

Avoid Recomputing the Same Thing

Calculate once, reuse many times. If the same sub-expression appears in multiple metrics, refactor it into a helper metric. Pigment calculates the helper once; all references read that result. This is far more efficient than each downstream metric performing its own identical computation and it ensures that fixing or optimising the logic only requires a change in one place.

Split Heavy Formulas

A single metric chaining too many operations can become slow or hit Pigment's calculation timeout. If you cannot read and understand a formula in one pass, consider splitting it. Breaking a calculation into intermediate metrics:

• Allows Pigment to execute independent parts in parallel.
• Enables caching of intermediate results.
• Makes it straightforward to identify which step is slow when profiling.
• Encapsulates business logic in auditable, individually testable units.

Example: In an FP&A model, compute personnel costs, overhead and depreciation in separate metrics, then sum them rather than using one formula. Each component can be profiled and optimised independently.

Beware of Heavy Functions

Iterative functions such as PREVIOUS, CUMULATE, MOVINGSUM and YEARTODATE lose scope on the time dimension and can be significantly slower on large dimensions. Use them only when necessary and isolate them in dedicated metrics. Prefer IFDEFINED over ISNOTBLANK. Prefer numeric or boolean logic over text manipulation wherever possible.

Worked Example: Inefficient vs. Optimised Formula

A Version dimension (Actual vs. Forecast). We want to compute Forecast Revenue based on last year's actuals and a growth rate, without affecting the Actual version.

Inefficient: a single metric handling all versions, forcing Pigment to evaluate the growth logic for every version on every change:

This formula recalculates across all version items whenever any input changes. If there are 5 versions, it evaluates 5x the cells that matter.

Optimised: a dedicated metric for Forecast only, scoped by an ISDEFINED guard:

Revenue_Actual is a separate, static metric containing loaded data. Revenue_Forecast only recalculates when a growth assumption changes, and only for accounts where GrowthRate is defined. Revenue_Actual is never touched by forecast logic. This separation is the version isolation pattern described in Part 3.

2. Dimensional Alignment and Formula Modifiers: Track Every Step

Every formula should be read as a sequence of dimensional transformations, not just arithmetic. Pigment's dimensional modifiers (BY, ADD, REMOVE, KEEP, SELECT, FILTER, EXCLUDE) adjust the dimensionality of a metric at each step. Chaining modifiers without tracking that transformation is one of the most common sources of both incorrect results and unexpected performance cost.

Two practical consequences follow. First, read modifiers as a transformation chain: the metric enters the formula with its original dimensionality, each modifier transforms it, and any extra dimensions remaining at the end will be summed or removed by Pigment, often producing unexpected numbers if you have not planned the alignment explicitly. Second, track dimensionality after every modifier. A BY aggregation moving from Month to Quarter and then back to Month has a very different execution profile from a BY applied to a metric already at Quarter grain. The Formula Playground is the tool to validate what Pigment actually infers before you deploy.

Be Explicit: Do Not Let Pigment Guess

If two operands do not share the same structure, Pigment will implicitly align them, often by adding or removing dimensions. This implicit alignment can expand the amount of data processed substantially. Consider the following:

• Tax Rate [Country, Year]
• Salary [Employee, Month]
• Required: Tax Amount [Employee, Month] where Country comes from Employee and Year comes from Month

Simply writing Salary * Tax Rate forces Pigment to resolve the join implicitly, which typically means materialising all combinations of Employee, Month, Country and Year before reducing. The correct approach is to align the dimensions explicitly before the multiplication. One valid pattern is to first resolve the Tax Rate to the Employee-Month grain using a lookup on the Employee's country and the Month's year, storing that result in a helper metric TaxRate_ByEmployee, and then multiply:

The Formula Playground shows you the inferred output structure of any formula. Use it to catch unintended dimension additions or removals before deployment. Always ask whether you are accidentally copying data across a dimension or summing when you mean to allocate.

3. Scoped Calculations and Avoiding Scope Collapse

Design Formulas to Preserve Scope

Some functions have an output scope of None, which forces broader computation than intended. Design formulas to preserve or restore scope explicitly:

• Start with conditions that define where the metric should exist. An IF at the start of a formula that returns BLANK for irrelevant intersections is the scope gate that protects everything downstream.
• Use FILTER or SELECT to eliminate irrelevant intersections early, before any aggregation.
• Use REMOVE, ADD or BY as explicit dimensional steps, not as quick fixes when you have not first verified the shape of the input data.

Isolate Scope-Breaking Functions

Iterative functions (PREVIOUS, CUMULATE, YEARTODATE and similar) lose scope on the dimension they iterate over. When you use these functions, the affected metric will recalculate for the entire time dimension whenever any time-related input changes, regardless of which specific period was updated. The containment strategy is to put these functions in a dedicated metric at the appropriate grain, so that the scope loss is bounded to that metric and does not cascade into every downstream calculation.

4. Transaction Lists: The Staging Metric Pattern

Aggregate Once, Reference Often

The key performance principle for Transaction Lists is that they should be scanned once. Create one staging metric per numeric property per Transaction List that performs the initial aggregation across all relevant dimensions:

Data_GL_Amount = GL.Amount[BY SUM: GL.Account, GL.Department, GL.Month]

All downstream planning metrics reference Data_GL_Amount with simple filters or selects, rather than scanning the raw GL list repeatedly. The expensive pass through the raw list is done once, and all downstream metrics are guaranteed to start from the same base numbers.

Minimise Computed Properties on Transaction Lists

Transaction Lists calculate one property at a time, so having many formula-driven properties increases recalculation time after every import. Three practical rules follow:

• Keep TL computed properties minimal. Only place formula logic in TL properties when it is necessary for the ingestion pattern itself.
• Convert stable formulas to values. When a property formula no longer needs to be dynamic, use Pigment's "convert formula to values" option. This eliminates the recalculation overhead for that property entirely.
• Push heavy transformations into metrics. Metrics benefit from parallel processing and caching. Property formulas on list items recompute sequentially whenever the list is updated.

Import Design for Performance

Imports are one of the most common triggers for performance problems in production. The import process itself is not the bottleneck; what the import causes downstream is. Every import that writes new data will trigger recalculation of all metrics that depend on that data. Several practices keep that recalculation cost manageable:

• Filter zeros and nulls at import time. Do not let zero-value lines pass into the model. Add a filter condition in the import mapping to exclude them. Zero values that enter the model densify staging metrics and every metric downstream.
• Import into staging metrics, not directly into planning metrics. Load raw transactional data into a Transaction List and aggregate from there using the staging metric pattern. This isolates the import's recalculation cost to the staging layer.
• Chunk large imports. If an import affects a large number of records, consider whether it can be split by period or entity. Smaller imports trigger smaller recalculation scopes, which reduces the wait time for users who are working concurrently.
• Schedule heavy imports for off-peak times. Imports that update large datasets (e.g. a full actuals reload at month-end) should be scheduled when concurrent user load is low. Pigment queues calculations, but a large import during peak usage degrades the experience for everyone on the application.

5. Refactoring and Modularity: Build for Clarity and Parallelism

Performance optimisation is an iterative process. The initial build gets the outputs correct. The refactoring phase reorganises those calculations for speed and maintainability.

Refactor Calculation Chains

When a set of formulas produces the right result, inspect the dependency graph. Ask whether the sequence could be rearranged or broken up to compute less at once. Splitting one metric into two independent metrics that feed a third allows Pigment to calculate them simultaneously. The goal is a chain that works fast, not just a chain that works.

Parallelise Where Possible

Pigment calculates independent metrics in parallel. If you can split a problem into two independent pieces, you get both computed at the same time. In a workforce model, calculate "Planned New Hires" in one metric and "Attrition" in another, then combine them in a third. Those two compute simultaneously if they share inputs but do not depend on each other. Smart partitioning aligned with business logic makes direct use of Pigment's multi-threaded backend.

Limit Formulas in List Properties

Use properties for lightweight lookups or labels only. Keep computational work in metrics, where parallel processing and caching apply. Property formulas on list items recompute sequentially whenever the list is updated, even if only one item changed.

Modular Models Are Easier to Audit

A model that is modular and refactored for performance is easier to audit and maintain. Each metric has a clear purpose, formulas are shorter and repeated logic is consolidated in one place. When a user reports an incorrect number, you can trace through a logical sequence of calculations rather than untangling a long formula. New team members can follow the flow, which matters for model longevity.

6. The Formula Playground: Iterate Before You Commit

Use It Before Creating Any Metric

The Formula Playground lets you test formulas and inspect data dimensions in Auto and Custom modes. Pigment recommends using Performance Insights inside the Formula Playground before creating a metric, so you can identify long-running patterns early, before they become structural problems that are expensive to undo. Treat the Formula Playground as a required step when writing any formula that involves dimensional modifiers, Transaction List aggregations or scoping conditions.

Validate the Inferred Output Structure

The Playground shows you what Pigment infers as the output structure of a formula. This is where unintended dimension additions or removals become visible before they reach production. If the inferred structure has more dimensions than you intended, that is a signal of implicit alignment work happening behind the scenes. Address it in the formula before creating the metric.

On Timeouts

Formulas can time out after approximately three minutes, resulting in an application error. This is not a threshold to approach. It is a sign that the formula is computing too much data. Any formula consistently taking more than a few seconds is a candidate for rethink at the scope and dimensionality level.

Part 2: Key Takeaways

• Start formulas with conditions that reduce the dataset before any allocation or aggregation. Narrow the scope first; let subsequent steps work on less data.
• Treat modifiers as dimensional transformations. Track dimensionality after each step and use the Formula Playground to validate the inferred output structure before deploying.
• Never let Pigment resolve dimensional alignment implicitly. Use helper metrics to pre-resolve joins to the correct grain before multiplication or aggregation.
• Create one staging metric per numeric TL property that performs the initial BY aggregation. All downstream metrics reference that result.
• Keep computed Transaction List properties minimal. Filter zeros at import time. For stable logic, convert formulas to values.
• Use the Formula Playground before creating any metric involving dimensional modifiers or Transaction List aggregations.
• A formula approaching the 3-minute timeout is a modelling problem, not a hardware problem.