Use Cases of Aggregation and Allocation

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

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's Revenue per Region, per Country or per Product and you want to calculate one based on the others.

In this case, we 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’s Dimension (N) (e.g Country) with a higher level (1) (e.g. Region).

In this case, you can aggregate data with the BY SUM, BY MIN, BY MAX and similar Dimension modifiers. They work like SUMIFS(), MINIFS(), MAXIFS() functions in Excel.

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'[BY 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 etc.) 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'[REMOVE 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 tells Pigment upon which Dimension you want to allocate values. You 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) feeds 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'[BY 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 rough estimate of the Revenue per Country, per Product.

One row of Revenue per Country (1) feeds 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'[ADD 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

Learn more

• Aggregating data in formulas