Aggregating data in formulas

Pigment provides the ability to perform complex aggregations and allocations, through the use of modifiers. We call them modifiers because they will 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 (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 will 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). It's 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 avaialable only on some data Types :

• For Number and Integer: SUM, AVG, MIN, MAX(returns the value of the first cell non blank) FIRSTNONZERO (returns the value of the first cell different than 0)

• For Date: MIN, MAX

• For Boolean: ANY, ALL

• For Text: TEXTLIST

and some other are available for all data Types:

• FIRST 

• LAST

• FIRSTNONBLANK

• LASTNONBLANK

• COUNT 

• COUNTBLANK

• COUNTALL

• COUNTUNIQUE

You can found more details on all those aggregator here.

List of Aggregators available by Type

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

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 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]

See also

Filtering data in Formulas