--- title: "Use Cases of Aggregation and Allocation" slug: "use-cases-aggregation-allocation" description: "Master data aggregation and allocation with key relationships between Dimensions to optimize your Metrics." updated: 2025-05-28T10:01:23Z published: 2025-08-22T12:00:18Z --- > ## 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. # Use Cases of Aggregation and Allocation In this article, we cover the four main relationships between Dimensions so that you become a master of aggregating and allocating data in your formulas. ## Overview Let's start with an overview: | **Relationship between Metrics’ Dimensions** | **Modification type** | **Dimension modifier** | **Aggregation / allocation methods** | | --- | --- | --- | --- | | **N->1** | Aggregation | `BY` | `SUM`,`MIN`,`MAX`,`COUNT` ... (default: `SUM`) | | **N->none** | Aggregation | `REMOVE` | `SUM`,`MIN`,`MAX`,`COUNT` ... (default: `SUM`) | | **1->N** | Allocation | `BY` | `CONSTANT`, `SPLIT` (default: `CONSTANT`) | | **none->N** | Allocation | `ADD` | `CONSTANT`, `SPLIT` (default: `CONSTANT`) | Let's deep dive into each use case with an example: let's say you want to know your business's `Revenue per Region`, `per Country` or `per Product` and you want to calculate one based on the others. In this case, we consider three **Dimension Lists:** ![](https://cdn.document360.io/e47cfe35-dc28-40c7-a083-6cf003073d8e/Images/Documentation/1749621d-379b-4b6f-8b82-40dfe21ad6bf(1).png) ## **Case 1: Aggregation with an N->1 relationship between Metrics’ Dimensions** There is an **N->1 relationship** when you want to match a Metric’s Dimension (N) *(e.g Country)* with a higher level (1*) (e.g. Region)*. In this case, you can **aggregate data**with the `BY SUM`, `BY MIN`, `BY MAX` and similar Dimension modifiers. They work like SUMIFS(), MINIFS(), MAXIFS() functions in Excel. **Illustrative example**: *Let's say you already know the*`Revenue per Country `*and you want to determine the*`Revenue per Region`*.* ![](https://cdn.document360.io/e47cfe35-dc28-40c7-a083-6cf003073d8e/Images/Documentation/fde2762a-77eb-4113-8909-a50c846fd561(1).png) *There are multiple rows of*`Revenue per Country`*(N) you want to sum together for each*`Revenue per Region`*(1)****.*** ![](https://cdn.document360.io/e47cfe35-dc28-40c7-a083-6cf003073d8e/Images/Documentation/41c0c875-4609-4a77-b5e2-d7a01331ef8a(1).png) *Now, let's carefully review the formula:* `'Revenue per country'[BY SUM: Country.Region]` 1. *You refer to your initial Metric :*`'Revenue per Country'` 2. *Then use the*`BY SUM`*Dimension modifier to*`SUM`*values when aggregating data* 3. *Then specify the Dimension you want to aggregate on :*`Region` 4. *And explicitly tell Pigment how to map a*`Country`*to a*`Region`*, by referring to*`Region`*as a Property of the Dimension*`Country`*:*`Country.Region` ## **Case 2: Aggregation with an N->None relationship between Metrics’ Dimensions** There is an **N->None relationship** when you want to remove a Dimension (*e.g. Product*) in the Metric you are **aggregating** *(e.g. Revenue per Country and per Product)*. In this case, you need to : - `REMOVE` the corresponding Dimension. - specify how to aggregate the data (e.g. `SUM`, `MIN`, `MAX`, `COUNT` etc.) with the `BY` Dimension modifier so that it fits with the remaining Dimensions. **Illustrative example:** *Let's say you already know the*`Revenue per Country, per Product`*and you want to determine the*`Revenue per Country`. ![](https://cdn.document360.io/e47cfe35-dc28-40c7-a083-6cf003073d8e/Images/Documentation/d2889a15-226b-4fbe-915c-0d55fc9dd4e5(1).png) *Multiple rows of*`Revenue per Product`*(N) should be grouped into each corresponding*`Revenue per Country`*(without Product).* ![](https://cdn.document360.io/e47cfe35-dc28-40c7-a083-6cf003073d8e/Images/Documentation/cb78f735-6ace-428c-8684-e3dce36fa6c9(1).png) *Now, let's carefully review the formula:* `'Revenue per Country, per Product'[REMOVE SUM: Product]` 1. *You refer to your initial Metric: '*`Revenue per Country, per Product`*'* 2. *Then use*`REMOVE SUM`*Dimension modifier to*`REMOVE`*a Dimension and sum the remaining values* 3. *Specify the Dimension you want to remove :*`Product` ## **Case 3: Allocation with a 1->N relationship between Metrics Dimensions** There is a **1->N relationship** when you want to **allocate** values from a Metric (*e.g. Revenue per Region*) to a Metric at a more granular level (*e.g. Revenue per Country*). In this case, the `BY` Dimension modifier tells Pigment upon which Dimension you want to allocate values. You also need to specify how you want to allocate data : - you can **allocate** **the same value across all items** with `BY CONSTANT`*(e.g. the Region Revenue is copied for each underlying Country).* - you can `SPLIT`**the value based on the number of items** with `BY SPLIT` *(e.g. the Region Revenue is equally distributed for underlying Countries).* **Illustrative example:** *Let's say you already know the*`Revenue per Region`*and you want a rough estimate of the*`Revenue per Country`. ![](https://cdn.document360.io/e47cfe35-dc28-40c7-a083-6cf003073d8e/Images/Documentation/5995e78d-0cf3-4713-811a-7126aa8c59bc(1).png) *One row of*`Revenue per Region`*(1) feeds several rows of*`Revenue per Country`*(N)****.*** ![](https://cdn.document360.io/e47cfe35-dc28-40c7-a083-6cf003073d8e/Images/Documentation/bff17af3-d7b0-4d51-b44e-9b72e6a1fada(1).png) *Each*`Revenue per Region`*(1) can also be*`SPLIT`*based on the number of*`Countries`*per*`Region`*.* *Now, let's carefully review the formula :* `'Revenue per Region'[BY SPLIT: Country.Region]` 1. *You refer to your initial Metric :*`'Revenue per Region'` 2. *Then use the*`BY SPLIT`*Dimension modifier to allocate values based on the number of countries per region* 3. *Then specify the Dimension you want to allocate to :*`Country` 4. *And explicitly tell Pigment how to map a*`Country`*to a*`Region`*, by referring to*`Region`*as a Property of the Dimension*`Country`*:*`Country.Region` ## **Case 4: Allocation with a none->N relationship between Metrics Dimensions** There is a **none->N** relationship when you want to `ADD` a new Dimension (*e.g Product*) to a Metric (*e.g. Revenue per Country*). In this case, you need to: - `ADD` the corresponding Dimension - specify how to allocate the data to the new Dimension modalities. As with the allocation use case, it works with the `CONSTANT` and `SPLIT` allocation methods **Illustrative example:** *Let's say you already know the*`Revenue per Country`*and you want a rough estimate of the*`Revenue per Country, per Product`. ![](https://cdn.document360.io/e47cfe35-dc28-40c7-a083-6cf003073d8e/Images/Documentation/6949eece-8e09-41d1-905e-ae8d1359c39e(1).png) *One row of*`Revenue per Country`*(1) feeds several rows of*`Revenue per Country, per Product `*(N). The revenue for each country is split based on the number of products.* ![](https://cdn.document360.io/e47cfe35-dc28-40c7-a083-6cf003073d8e/Images/Documentation/0b89da19-6818-4e70-8a2a-f7cbaa769d45(1).png) *Now, let's carefully review the formula:* `'Revenue per Country'[ADD SPLIT: Product]` 1. *You refer to your initial Metric:*`'Revenue per Country'` 2. *Then use the*`ADD SPLIT`*Dimension modifier to allocate values based on the number of products* 3. *Then specify the Dimension you want to allocate to:*`Product` ### **Learn more** - [Aggregating data in formulas](/v1/docs/aggregating-data-in-formulas)