Pigment provides powerful operators to perform complex aggregations and allocations. We call them dimension modifiers because they will modify the dimensionality of objects.
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 page, 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 at some point 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 covered in a single function by 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
to aggregate the Orders
data by Customer
, Product
and Month
, to pivot the dimensions and to 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 not specified for metric of type number, Pigment applies a SUM by default.
Aggregation methods
Other aggregation methods are :

For number and integer: SUM, AVG, MIN, MAX, FIRSTNONBLANK (returns the value of the first cell non blank) FIRSTNONZERO (returns the value of the first cell different than 0)

For date: MIN, MAX, FIRSTNONBLANK

For boolean: ANY, ALL, FIRSTNONBLANK

For text and dimension: FIRSTNONBLANK
How to count?
For all types above you can count values:

COUNT to count all non blank cells,

COUNTBLANK to count only blank cells,

COUNTALL to count all values, even blank/empty cells)

COUNTUNIQUE to count only distinct values (and not blank)
The resulting metric must be set as number or integer.
Aggregating data from metrics
Aggregation of a metric's data works the same way but instead of referencing the list, you need to the metric name.
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
See also:
Comments
0 comments
Please sign in to leave a comment.