--- 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." tags: ["Modeling Formulas", "Data Aggregation", "Revenue Calculation"] updated: 2025-05-28T10:01:23Z published: 2025-08-22T12:00:18Z canonical: "kb.pigment.com/use-cases-aggregation-allocation" --- > ## 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?sv=2026-02-06&spr=https&st=2026-11-22T22%3A38%3A05Z&se=2026-11-22T23%3A38%3A05Z&sr=b&sp=r&sig=HS5akHZnSjM3fBliBAUqNz9YqwIHPue30d%2B0kPvn97Y%3D) ## **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?sv=2026-02-06&spr=https&st=2026-11-22T22%3A38%3A05Z&se=2026-11-22T23%3A38%3A05Z&sr=b&sp=r&sig=um4PDS0%2BVc%2Fw%2B1mdHkEIis7rmewnsucU7ml7yTEeE7g%3D) *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?sv=2026-02-06&spr=https&st=2026-11-22T22%3A38%3A05Z&se=2026-11-22T23%3A38%3A05Z&sr=b&sp=r&sig=t273ydTdRi3GNObRrm2uA0eCic3TcDBPjHjNiwvAA0c%3D) *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?sv=2026-02-06&spr=https&st=2026-11-22T22%3A38%3A05Z&se=2026-11-22T23%3A38%3A05Z&sr=b&sp=r&sig=xuALxQiobu67jQ3tFI%2F8t%2BqUs%2BIjv2S5y5D1rW6PGSs%3D) *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?sv=2026-02-06&spr=https&st=2026-11-22T22%3A38%3A05Z&se=2026-11-22T23%3A38%3A05Z&sr=b&sp=r&sig=v%2FWHggrXcGmv%2Bd1cfnm%2B3%2FcG5lYCfVucuQyMb1L%2FJqA%3D) *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?sv=2026-02-06&spr=https&st=2026-11-22T22%3A38%3A05Z&se=2026-11-22T23%3A38%3A05Z&sr=b&sp=r&sig=mrzQ67hAb%2BfAu9Z4kYJSuclBZTSnHYbuSiM50OsMQTg%3D) *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?sv=2026-02-06&spr=https&st=2026-11-22T22%3A38%3A05Z&se=2026-11-22T23%3A38%3A05Z&sr=b&sp=r&sig=S8T66wN9VuJ4gJQ81iaoNq39pMHPNbKeFhJl9YRhMxI%3D) *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?sv=2026-02-06&spr=https&st=2026-11-22T22%3A38%3A05Z&se=2026-11-22T23%3A38%3A05Z&sr=b&sp=r&sig=GdNl%2F6J49QbaGApTIT3H%2BdUB2O%2FOcBhkq2szKCe6QiE%3D) *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?sv=2026-02-06&spr=https&st=2026-11-22T22%3A38%3A05Z&se=2026-11-22T23%3A38%3A05Z&sr=b&sp=r&sig=Mg4ZcfpFGwqsp2xZL1OrbOc4pNvTHjC46o9wQUftPB4%3D) *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)