---
title: "Aggregate Data in Formulas"
slug: "aggregate-data-in-formulas"
description: "Learn how to perform complex data aggregations and allocations in Pigment using modifiers and the powerful BY keyword for effective analysis."
tags: ["aggregating data in formulas", "Metrics Calculation", "Modeling Formulas", "Pigment Modifiers", "Reporting Data visualization"]
updated: 2025-10-21T13:23:41Z
published: 2025-10-21T13:23:41Z
canonical: "kb.pigment.com/aggregate-data-in-formulas"
---

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

# Aggregate Data in Formulas

Pigment provides the ability to perform complex aggregations and allocations, through the use of modifiers. We call them **modifiers** because they modify the dimensionality or data of an object within a formula.

For both aggregations and allocations, we decided to use the same magical keyword `BY`. It is simple to remember since we usually say that we:

- aggregate data **by** a Dimension (C*ountry Revenue aggregated* ***by*** *Region, Employee* ***by*** *Team, etc.*)
- allocate data **by** a Dimension (R*egion Target allocated* ***by*** *Country, Grade Salary* ***by*** *TBH, Annual Target* ***by*** *Month, etc.*)

In this specific article, we focus on **aggregations** Σ.

## Grouping Views or Formula Aggregation?

Even if you can perform aggregations in Views by **grouping** data like in a Pivot Table in Excel, you will also need to aggregate data stored in [Transactional lists](/v1/docs/understand-dimension-transaction-lists) or [Metrics](/v1/docs/measure-data-metrics) to match the granularity of data in other Metrics.

Most of the examples below would be equivalent to the SUMIF or AVERAGEIF functions in Excel. But Pigment provides other aggregation methods not available in a single function in Excel.

## Aggregating data from a List

Let's say you store a Transactions List called `Orders` in which you find columns such as: `Month`, `Customer`, `Product`, `Quantity` and `Amount`.

![](https://cdn.document360.io/e47cfe35-dc28-40c7-a083-6cf003073d8e/Images/Documentation/42b28257-0f74-49d0-9c82-8e895e95ce1e.png)

Now, you may want to create a **Metric** called `Orders Revenue` that aggregates the `Orders` data by `Customer`, `Product` and `Month`, to pivot the Dimensions and include them in other calculations (like the calculation of a Gross Margin).

This **Metric** would be set with the type number and the desired Dimensions (`Customer`, `Product` and `Month`). Its formula would be:

`Orders.Amount[BY SUM: Orders.Customers, Orders.Product, Orders.Month]`

*Which can be read as: in the list* `Orders`*, take the Property* `Amount` *and* ***SUM*** *it* ***BY*** *the* `Orders`*'* `Customer`*,* `Product` *and* `Month`*.*

![](https://cdn.document360.io/e47cfe35-dc28-40c7-a083-6cf003073d8e/Images/Documentation/d48a9a1c-8cdf-484c-a56a-a858292dda3e.png)

In this example, you see the method of aggregation just after the BY, [BY SUM: ... ]. If this method is not specified for a **Metric** of type number, Pigment applies a SUM by default.

## Aggregation methods

Some **aggregation methods** are available only on some data types :

- For Number and Integer: **SUM, AVG, MEDIAN, STDEVS, STDEVP, MIN, MAX, FIRSTNONZERO** (returns the value of the first cell different than 0)
- For Date: **MIN, MAX**
- For Boolean: **ANY, ALL**
- For Text: **TEXTLIST**

and some others are available for all data Types:

- **FIRST**
- **LAST**
- **FIRSTNONBLANK**
- **LASTNONBLANK**
- **COUNT**
- **COUNTBLANK**
- **COUNTALL**
- **COUNTUNIQUE**

You can find more details on all those aggregators [here](/v1/docs/by-modifier).

## List of Aggregators available by Type

|  | Number & Integer | Boolean | Date | Text | Dimension |
| --- | --- | --- | --- | --- | --- |
| SUM | X |  |  |  |  |
| AVG | X |  |  |  |  |
| MEDIAN | X |  |  |  |  |
| STDEVS | X |  |  |  |  |
| STDEVP | X |  |  |  |  |
| MIN | X |  | X |  |  |
| MAX | X |  | X |  |  |
| ANY |  | X |  |  |  |
| ALL |  | X |  |  |  |
| TEXTLIST |  |  |  | X |  |
| FIRST | X | X | X | X | X |
| LAST | X | X | X | X | X |
| FIRSTNONBLANK | X | X | X | X | X |
| LASTNONBLANK | X | X | X | X | X |
| FIRSTNONZERO | X |  |  |  |  |
| LASTNONZERO | X |  |  |  |  |
| COUNT | X | X | X | X | X |
| COUNTBLANK | X | X | X | X | X |
| COUNTALL | X | X | X | X | X |
| COUNTUNIQUE | X | X | X | X | X |

## Aggregating data from Metrics

Aggregation of a **Metric's** data works the same way, but instead of referencing the **List**, you need to reference the **Metric** name.

Let's say that our `Product List` has a Property called `Category`.

We may want to create a **Metric** called `Category Revenue` that stores the data from above by Product Category.

`'Orders Revenue'[BY SUM: 'Product'.'Category']`

*Which can be read as : using the data from the* ***Metric*** `Orders Revenue`***,*** *return the SUM BY the Property* `Category` *of the* ***List*** `Product`

![](https://cdn.document360.io/e47cfe35-dc28-40c7-a083-6cf003073d8e/Images/Documentation/1f1ce28c-53e3-41d1-9c5a-68c799c9095c.png)

### Referencing an aggregated total

When trying to reference an aggregated total in a Metric the [Remove modifier](/v1/docs/remove-modifier) can be used to remove the dimensions while still returning the value of the total. By default it will pull the sum aggregated total however, you can use the aggregators listed above to use different methods.

For example, here is a table with a source Metric called **Data Country x Month** with the `Country` and `Month` dimensions and I wanted to pull in the totals for all countries combined in each month into the highlighted Metric.

The formulas references the Metric and use the Remove modifier to remove the `Country` dimension and give the summed totals.

Here is the formula `'Data Country x Month'[REMOVE sum: Country]`

![](https://cdn.document360.io/e47cfe35-dc28-40c7-a083-6cf003073d8e/Images/Documentation/b61c855f-b648-4796-b491-beebc27671fa.png)

> [!TIP]
> 🎓 **Resources**
> 
> **More of a hands-on learner?**
> 
> Talk to your Customer Success Manager about downloading the Functions and Modifiers in Pigment Application into your workspace. It includes examples of every Function and Modifier in Pigment! Refer to the [Interactive Source to Target Mapping Tool](https://academy.gopigment.com/source-to-target-mapping-tool) for quick reference information on aggregation methods.