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 Dimensions 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 (Country Revenue aggregated by Region, Employee by Team, etc.)

• allocate data by a Dimension (Region 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 or 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.

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.

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.

List of Aggregators available by Type

Order-sensitive aggregators and Dimension order

Some aggregation methods are order-sensitive: the result depends on the order in which the Dimensions are aggregated. These aggregators are:

• FIRST

• LAST

• FIRSTNONBLANK

• LASTNONBLANK

• FIRSTNONZERO

• LASTNONZERO

• TEXTLIST

When one of these aggregators removes a single Dimension, there is no ambiguity: Pigment uses the order of the remaining Dimension.

When it aggregates more than one Dimension at once, the formula alone doesn't say which Dimension is aggregated first, and different orders can return different values.

For example, imagine that the Revenue Metric has Country and Month Dimensions. Aggregating both at once with FIRSTNONBLANK can mean only one of the following two alternatives:

• take the first non-blank value across Country, then across Month.

• take the first non-blank value across Month, then across Country.

These two readings can return different values.

How Pigment resolves aggregation ambiguity

To keep results explicit and stable, Pigment automatically completes your formula with a clause using the ON operator, which lists the aggregated Dimensions in the order in which they are applied. The clause is written into the formula itself: you can see it, review it and change it at any time.

You write:

Revenue[BY FIRSTNONBLANK: Country, Month -> 'Region Mapping']

Pigment saves:

Revenue[BY FIRSTNONBLANK ON (Country, Month): Country, Month -> 'Region Mapping']

Your results do not change. The ON clause Pigment inserts reproduces the order that was already being applied to your formula. It only makes that order explicit and editable.

Choosing a different order

If you want a different order, edit the clause manually by changing the order of the Dimensions inside the bracketed expression following ON. In the following formula, Country and Month have been reversed:

Revenue[BY FIRSTNONBLANK ON (Month, Country): Country, Month -> 'Region Mapping']

An ON clause that you edit always takes priority and Pigment does not overwrite it.

Which modifiers are affected

ℹ️ Note

The ON clause is added when the formula is compiled. If you later add a Dimension to the source Block, the existing clause is not rewritten. Review it to confirm that the order still matches your intent.

Aggregate 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

Referencing an aggregated total

When trying to reference an aggregated total in a Metric, the Remove modifier can be used to remove the Dimensions while still returning the value of the total. By default it pulls 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 formula references the Metric and uses the Remove modifier to remove the Country Dimension and give the summed totals.

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

🎓 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 for quick reference information on aggregation methods.