Description

Aggregates or allocates data across Dimensions based on one or more mapping expressions.

Syntax

The syntax is the same for allocation or aggregation:

source_expression[BY aggregation_method: mapping_expression_1[, mapping_expression_2]]

source_expression[BY allocation_method: mapping_expression_1[, mapping_expression_2]]

Return type

Depends on the source data type and aggregation / allocation method. For more information, see Aggregation and allocation methods.

What is a mapping expression?

A mapping expression defines a relationship between one (or several) source list(s) (Dimension or Transaction List) and a target Dimension. It can be a List Property or a Metric with data type set to Dimension. For more information on mapping Metrics, see Make a mapping Metric.

For example, a simple mapping expression could define the relationship between countries and regions in one of the following ways:

• a Property of type Dimension (Region) in the Country Dimension: Country.Region

• a Metric of type Dimension (Region) defined on the Country Dimension

An advanced mapping expression could map several Dimensions to a target Dimension. For more information, see Advanced: BY with multiple Dimensions.

Aggregating vs allocating using BY

The same mapping expression, for example Country.Region, can be used in two ways:

1. Aggregate data from source list to target Dimension.

   • Example: summing country-level sales by region

   • Relationship: many-to-one (multiple source Items map to one target)

   • Aggregation methods: choose from supported aggregation methods.
     Default is SUM for data types Number and Integer. For all other types, the formula requires an explicit aggregation_method.

   • Its symbol in Pigment is .

2. Allocate data from target Dimension to source list.

   • Example: distributing a regional sales target to each country

   • Relationship: one-to-many (one target Item maps to multiple sources)

   • Allocation methods: choose from

     • CONSTANT: assigns the same value to each Item.

     • SPLIT: distributes values proportionally.

     Default is CONSTANT.

   • Its symbol in Pigment is .

Example: many-to-one (aggregation)

Let’s consider a first situation:

• Sales_by_country is a Metric defined on Dimension Country.
  

• The Country Dimension has a Property of type Dimension (Region): Country.Region.
  

  

The relationship of Country to Region is many-to-one, as multiple Country Items (for example France, Spain, UK) refer to the same Region Item (EMEA).

Sales_by_country[BY SUM: Country.Region] AGGREGATES countries’ sales within the same region, using a SUM aggregation. The Region Dimension replaces the Country Dimension in the output data.

Dimension modifications and aggregation methods can be visualized as above by hovering over the word BY in the formula.

Example: one-to-many (allocation)

Let’s consider a slightly different situation:

• Sales_by_region is a Metric defined on the Region Dimension.

• The Country Dimension has a Property of type Dimension (Region) (Country.Region)

This is the same mapping expression as in the previous section, but this time the source Metric is defined on Dimension (Region) instead of Dimension (Country).

The relationship of Region to Country is one-to-many, as a single Region Item (for example, EMEA) refers to multiple Country Items (France, Spain, UK).

Sales_by_region[BY CONSTANT: Country.Region] ALLOCATES each Region’s sales to its related Countries, using a CONSTANT allocation. The Country Dimension replaces the Region Dimension in the output data.

Dimension modifications and aggregation methods can be visualized as above by hovering over the word BY in the formula.

ℹ️ Note

The two formulas pictured above are similar, but behave completely differently due to the Dimensions of the source data:

• If source_data is defined on the Country Dimension, it aggregates Country data by Regions (using the SUM aggregator)

• If source_data is defined on the Region Dimension, it allocates Region data to Countries (using the CONSTANT allocator)

Use BY with Transaction Lists

Even if Transaction Lists are not defined on specific Dimensions (unlike Metrics), the BY modifier behaves with the same logic as for Metrics with Dimensions.

The most common way to use BY with Transaction Lists is to aggregate their data in a target Metric. To do this, you can use a Property as the source_expression and another Property of type Dimension as mapping_expression.

TL.NumberProperty[BY SUM: TL.PropertyDimensionA] SUMS values in the Transaction List TL.NumberProperty by Items in Dimension A, using the mapping expression TL.PropertyDimensionA.

The output of this formula is defined on Dimension A and has type Number.

Example: Sales_2025 is a Transaction List with one Property of type Dimension (Country) and another of type Dimension (Month):

The formula Sales_2025.Volume[BY: Sales_2025.Country] returns the SUM of sales per Country, using data defined on Dimension (Country):

Advanced: BY with multiple mapping expressions

You can use the BY modifier with multiple mapping expressions.

source_expression[BY aggregation_method: mapping_expression_1[, mapping_expression_2]]

Example:

• source_expression is a Metric defined on Dimension A and Dimension B.

• mapping_expression_1 is a relationship between Dimension A and Dimension C (DimensionA.DimensionC)

• mapping_expression_2 is a relationship between Dimension B and Dimension D (DimensionB.DimensionD)

source_expression[BY aggregation_method: mapping_expression_1, mapping_expression_2] simultaneously AGGREGATES Dimension A Items’ data by each Dimension C Item and Dimension B Items’ data by each Dimension D Item.

Advanced: BY with multiple Dimensions

If you need to map two source Dimensions (Dimensions A and B) to a target Dimension (Dimension C), you can do this by creating a mapping expression of type Dimension (C) defined on Dimension A and Dimension B.

Example:

• a mapping expression Mapping_Metric defined on the Dimensions Product and Region, with data type Dimension (Team), assigns teams to specific Product x Region combinations:

• a source expression is defined on the following Dimensions: Region, Product and Month:
  

Source_Metric[BY: Mapping_Metric] AGGREGATES data by Team for each Month. Dimensions Product and Region are removed.

Taking Team A as an example:

• Team A in the mapping expression maps to the Product 1 cell under EMEA, the Product 1 cell under APAC and all three Products under NA

• For January, the formula returns 1 for EMEA, 1 for APAC and 3 for NA, totalling 5.

Advanced: using BY →

As the above example shows, when applying BY, source data Dimensions that are transformed through the mapping logic are not included in the output data.

BY → (often referred to as the “arrow” function) allows you to specify which Dimensions should be replaced under the mapping logic, retaining all the unspecified Dimensions in the output data.

Syntax:

source_expression[BY aggregation_method: source_metric_dimension 1, source_metric_dimension 2 -> mapping metric]

The source_metric_dimension parameters are optional and define which, if any, Dimensions are to be replaced by the mapping Metric:

• If source_metric_dimension 1 etc. are left undefined, all source Metric Dimensions are kept in the output data.

• If a source_metric_dimension is defined, this Dimension is replaced in the output data under the mapping logic and all others are retained.

To extend the example from the section above:

• We now want to keep Region in the output data.

• We use the “BY →” modifier:
  Source_Metric[BY: Product → Mapping_Metric].

This syntax only replaces the Product Dimension under the Mapping_Metric logic, keeping the Region Dimension in the output data:

Hints and troubleshooting

You can hover over any BY keyword in a formula to open an instant, contextual hint showing you exactly what's happening with your Dimensions and warnings that help troubleshoot your formula.

The hint includes information on:

• Dimensions or Transaction Lists that are replaced when using the BY modifier.

• Whether it is an Aggregation or Allocation.

• The aggregation_method  or allocation_method used.

If you use the BY → Modifier, you also see which Dimensions are kept because of the “→”.

Warnings

Warnings are displayed if your BY formula generates conflicting Dimensions or if your mapping expression does not include Dimensions compatible with your source_expression:

Source expression not defined on Dimensions of mapping expression

• Dimension B has a one-to-many relationship with Dimension A (for example, DimensionA.DimensionB)

• source_expression is not defined on either Dimension

source_expression[BY: DimensionA.DimensionB] implicitly adds Dimension B to source_expression and applies the CONSTANT allocation B to A.

Source expression defined on Dimensions of mapping expression

• Dimension A has a many-to-one relationship with Dimension B (for example, DimensionA.DimensionB)

• source_expression is defined on Dimension A and Dimension B

source_expression[BY: DimensionA.DimensionB] applies the SUM aggregation of A to B, which creates a conflict in the output data since source_expression is already defined on Dimension B. Only matching values are kept in the output data.