In the previous article about aggregating data in formulas, we focused on how to:

• summarize data at a less granular level (e.g. aggregate a Transactions list into a Revenue per Country Metric).

• match Metrics having different granularities (e.g revenues at a country level and at a region level).

For example, aggregating revenues to a Region level corresponds to an N->1 relationship ('N to one relationship') because you have N countries for each 1 region:

N ->1 = France + United Kindom -> EMEA

In this article, we will cover the 4 main relationships between Dimensions so that you become a master of aggregating & allocating data in your formulas 👑.

Table of contents

Overview

Let's start with an overview:

Let's deep dive into each use case with an example: let's say you want to know your business' Revenue per Region, per Country or per Product and you want to calculate one based on the others.

In this case, we will consider three Dimension Lists:

Case 1: Aggregation with an N->1 relationship between Metrics Dimensions

There is an N->1 relationship when you want to match a Metric Dimension (N) (e.g Country) with a higher level (1) (e.g. Region). 

In this case, you can aggregate data thanks to the BY SUM, BY MIN, BY MAX and similar Dimension modifiers. They work like SUMIFS(), MINIFS(), MAXIFS() functions in excel. There are many other aggregation methods than SUM, MIN and MAX that are listed in this article.

Illustrative example:

Let's say you already know the Revenue per Country and you want to determine the Revenue per Region.

There are multiple rows of Revenue per Country (N) you want to sum together for each Revenue per Region (1).

Now, let's carefully review the formula :

'Revenue per country'cBY 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 ...) like 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.

Multiple rows of Revenue per Product (N) should be grouped into each corresponding Revenue per Country (without Product).

Now, let's carefully review the formula:

'Revenue per Country, per Product'eREMOVE 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 will tell Pigment upon which Dimension you want to allocate values. You will 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.

One row of Revenue per Region (1) will feed several rows of Revenue per Country (N).

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'lBY 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 roughly estimate of the Revenue per Country, per Product.

One row of Revenue per Country (1) will feed several rows of Revenue per Country, per Product (N). The revenue for each country is split based on the number of products.

Now, let's carefully review the formula:

'Revenue per Country'tADD 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

See also

Aggregating data in formulas