Grouped reducer formulas

Availability

Author grouped reducer formulas through the V3 document or business-question API/MCP contract. The editor authors one formula, the index described in Index in the editor; it shows any other formula as a custom formula and keeps it unchanged. MCP clients need tool contract 7.6.0 or later. If a client has cached older schemas, initialize again and call tools/list to discover the formula and selection parameter inputs.

Index sales to a baseline

Select a simple Sales measure, group it by year and region, then compare each completed value with its region’s 2025 baseline. A baseline divided by 100 makes the baseline year return 100. This is one calculation; no conditional measure wrapper is needed.

The example assumes a stored Year dimension with dateLevel: year, a Region dimension, and a simple Sales measure. A year grouped as a date uses a date baseline, not the integer 2025.

dimensions:
  - { key: year, reference: { dimension: Year } }
  - { key: region, reference: { dimension: Region } }
measures:
  - key: index
    reference: { measure: Sales }
    parameters:
      baselineYear: { dataType: date, default: '2025-01-01' }
      indexScale: { dataType: float, default: 100 }
    advancedCalculation:
      compareToTotal:
        comparison: ratio
        aggregate: SUM
        partitionBy: [{ reference: { dimension: region } }]
        formula:
          expression: 'if({year} = {baseline}, {amount} / {scale}, null)'
          year: { dimension: { reference: { dimension: year } } }
          amount: { groupedValue: true }
          baseline: { reference: { parameter: baselineYear } }
          scale: { reference: { parameter: indexScale } }

formula runs over completed grouped values before SUM, AVG, MIN or MAX. groupedValue reads the owning calculation. A dimension operand names a selected dimension key. Parameter operands read the owning selection’s parameters; execution overrides use the existing parameters.all or parameters.measures.<selection key> contract. Stored reusable measures use dimension: {queryDimensionIndex: 0} and query-relative partition positions instead.

total reduces the formula on its current partition. compareToTotal reduces it on the baseline partition; an explicit currentPartitionBy also applies the formula to the current reduction. The unaggregated current side retains its completed value. Missing, null or zero baselines return null. Typed values, scalar operators and scalar UDF operands reuse the document expression shapes.

Formulas cannot use percentile or control-value reducers, raw fields, other measures, or a same-owner period, nested, moving, running, rank, row-comparison or series calculation. Invalid operands, unknown parameters and unavailable combinations are rejected.

Stored totals without formulas can use percentile or control-value reducers. Import refuses a stored grouped reducer combined with a window or series stage on the same calculation; an import that withholds unsupported items withholds it instead. Stored grouped reducers, with or without formulas, cannot share a measure with a period comparison, period-axis binding or nested calculation; validation and publishing reject those combinations.

Native V3 documents retain typed scalar UDF calls. Importing a layer in the earlier engine format fails with an explicit error when a reducer formula uses a function call the import cannot convert; the formula is never dropped.

Index in the editor

In a business question, choose Compare to total on a measure, then turn on Index to a base value below Comparison group. Pick the Index axis, one of the selected dimensions, and the Base value to index against, such as 2025 for a year part. Changing the axis clears the base value.

Each value is divided by its comparison group’s total at the base value. The base reads 100 when every other selected dimension is in the comparison group; otherwise each row is divided by the group’s total across those other dimensions. A base missing from the data returns empty values, and so does an index with no base value chosen yet, which validation reports as a warning.

While the index is on, the comparison type stays Ratio, Control value is unavailable, and the index axis cannot also be in the comparison group. The editor stores the base and the scale of 100 as typed values in the formula, not as selection parameters.

The switch is unavailable when the selection has:

• a period comparison, a period-axis binding or a nested calculation;
• a percentile or control-value reducer;
• a series value or a moving, running, rank or row-comparison calculation;
• every selected dimension in the comparison group;
• a custom formula the editor did not author, which it keeps unchanged.

See the business question YAML reference for selection keys, ordinary formulas, sorting and execution parameters.