---
title: "Use the BY Modifier to aggregate or allocate data "
slug: "by-modifier"
description: "Learn how to aggregate or allocate data using Pigment’s BY modifier, defining relationships between Dimensions for effective data transformation."
tags: ["BY", "BY Modifier", "formula syntax", "functions", "Modeling Formulas", "Modifiers", "Pigment Blocks", "Pigment Boards", "Pigment Functions"]
updated: 2026-04-29T07:37:02Z
published: 2026-04-29T07:37:02Z
---

> ## Documentation Index
> Fetch the complete documentation index at: https://kb.pigment.com/llms.txt
> Use this file to discover all available pages before exploring further.

# BY modifier

# 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](/v1/docs/aggregators-and-allocators).

## **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](/v1/docs/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](/v1/docs/by-modifier#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](/v1/docs/aggregators-and-allocators). 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 ![](https://cdn.document360.io/e47cfe35-dc28-40c7-a083-6cf003073d8e/Images/Documentation/image(314).png).
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

Default is **CONSTANT**.
    - CONSTANT: assigns the same value to each Item.
    - SPLIT: distributes values proportionally.
  - Its symbol in Pigment is ![](https://cdn.document360.io/e47cfe35-dc28-40c7-a083-6cf003073d8e/Images/Documentation/image(315).png).

### Example: many-to-one (aggregation)

Let’s consider a first situation:

- `Sales_by_country` is a Metric defined on Dimension **Country**. ![](https://cdn.document360.io/e47cfe35-dc28-40c7-a083-6cf003073d8e/Images/Documentation/image(312).png)
- The **Country**Dimension has a Property of type Dimension (**Region**): `Country.Region`.

![](https://cdn.document360.io/e47cfe35-dc28-40c7-a083-6cf003073d8e/Images/Documentation/image(313).png)

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.

![](https://cdn.document360.io/e47cfe35-dc28-40c7-a083-6cf003073d8e/Images/Documentation/image(309).png)

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.

![](https://cdn.document360.io/e47cfe35-dc28-40c7-a083-6cf003073d8e/Images/Documentation/image(310).png)

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

> [!NOTE]
> ℹ️ 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.

![](https://cdn.document360.io/e47cfe35-dc28-40c7-a083-6cf003073d8e/Images/Documentation/image(311).png)

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

![](https://cdn.document360.io/e47cfe35-dc28-40c7-a083-6cf003073d8e/Images/Documentation/image(316).png)

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

![](https://cdn.document360.io/e47cfe35-dc28-40c7-a083-6cf003073d8e/Images/Documentation/image(317).png)

![](https://cdn.document360.io/e47cfe35-dc28-40c7-a083-6cf003073d8e/Images/Documentation/image(318).png)

## 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:

![](https://cdn.document360.io/e47cfe35-dc28-40c7-a083-6cf003073d8e/Images/Documentation/image(319).png)

- a source expression is defined on the following Dimensions: **Region**, **Product**and **Month**: ![](https://cdn.document360.io/e47cfe35-dc28-40c7-a083-6cf003073d8e/Images/Documentation/image(320).png)

`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.

![](https://cdn.document360.io/e47cfe35-dc28-40c7-a083-6cf003073d8e/Images/Documentation/image(321).png)

![](https://cdn.document360.io/e47cfe35-dc28-40c7-a083-6cf003073d8e/Images/Documentation/image(322).png)

## 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:

![](https://cdn.document360.io/e47cfe35-dc28-40c7-a083-6cf003073d8e/Images/Documentation/image(323).png)

![](https://cdn.document360.io/e47cfe35-dc28-40c7-a083-6cf003073d8e/Images/Documentation/image(324).png)

## 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.

![](https://cdn.document360.io/e47cfe35-dc28-40c7-a083-6cf003073d8e/Images/Documentation/image(325).png)

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

![](https://cdn.document360.io/e47cfe35-dc28-40c7-a083-6cf003073d8e/Images/Documentation/image(326).png)

## 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**.

![Explains dimension allocation in data mapping with highlighted implicit dimension warning.](https://cdn.document360.io/e47cfe35-dc28-40c7-a083-6cf003073d8e/Images/Documentation/Screenshot 2026-04-22 at 16.55.54.png)

### 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.

![Warning about conflicting dimensions in data aggregation process with replaced dimensions shown.](https://cdn.document360.io/e47cfe35-dc28-40c7-a083-6cf003073d8e/Images/Documentation/Screenshot 2026-04-22 at 16.53.48.png)