Meaningful Ratios: Mastering Advanced Aggregators

1. Forecast Table

In this first example, you’ll build a year-over-year growth table with your actual results for Y0 and your forecasts for Y+1. It’ll sum up your forecasts for each product line based on the growth you expect on your Y0 actuals.

For this, create the following Metrics and Tables:

Growth Forecast Metric

This Metric holds manual inputs giving the expected growth.

1. Create a Metric called Growth Forecast with Number data type. Its Dimensions are Product line and Year.
2. Input the forecasts by product line in the form of year-over-year expected growth percentages:

Forecast volume Metric

1. Take your Metric of Actuals for the current year and duplicate it.
2. Rename the copy Forecast volume and write in a formula of the type “Actuals * (1 + Growth Forecast)”.

The Metric populates with volume data for the year being forecast, reflecting the growth inputs:

Forecast data table

1. Create a Table for forecast data and add the following Metrics in this order:
   1. Actuals
   2. Forecast Volume
   3. Growth Forecast
2. In Pivot, set the Metrics up as Columns. The example also has some product hierarchy levels, Casual Wear L1 and L2, for illustration:

Set up aggregators

The topline numbers are still blank in our Topline data table. Using the tools in the 𝝨 menu, found in the top toolbar, you can set up aggregations for these.

First the simple aggregators:

1. Select 𝝨 from the top toolbar.
2. Add Sum aggregators to all three Dimensions listed for the first two Metrics, six in total: 

   

    This fills the blanks for overall total and first- and second-level aggregations.
3. Add advanced aggregators to fill the growth measures:
   1. In the 𝝨 menu, select the third Metric, Growth Forecast.
   2. Against each Dimension, instead of selecting the Sum aggregator, scroll down to the bottom of the drop-down and select Advanced aggregator.
   3. In the pane that opens, for Calculation, select Growth.
   4. Select your Forecast volume Metric for Metric A and your Actuals Metric for Metric B.
   5. Repeat for all three Dimensions. At the end your Table and aggregator panel should look like this: 

2. Rule of 40

SaaS businesses sometimes use the "Rule of 40" as a key performance indicator. The rule says a company's combined year-over-year growth rate and profit margin should be more than 40%.

In this example, you'll set up a Table to check Rule of 40 compliance by month, across two distinct operational regions—and at the topline level, too.

Revenue Y0 and Y-1, and Costs

1. Create a Metric called R40 Revenue Y0, with Number data type. Its Dimensions are Month and Country. Input your monthly revenues for the current year, Y0.
2. Duplicate it and rename the copy R40 Revenue Y-1. Add a formula such as 'Revenue Y0' [BY CONSTANT: Month - 12] to become your Metric of revenues for last year, changing input values as desired.
3. Duplicate that and rename the copy R40 Costs. Input your monthly costs for the current year.
4. Add these to a Table.
5. In the Pivot menu, ensure that the Country Dimension is moved up to Pages.

It should look similar to the below:

Indicators

You now have the data needed to generate three indicators. Create three more Metrics named as in the below list, with the same Dimensions: Country and Month.

1. For Margin% add a formula: ('R40 Revenue Y0'/'R40 Costs')-1
2. For Revenue growth% add a formula: ('R40 Revenue Y0'/'R40 Revenue Y-1')-1
3. For Rule of 40 add a formula: 'R40 Margin%' + 'R40 Revenue growth%'

Notice that with ‘All’ countries selected in the Page selector, the Margin% and Revenue growth% are showing the sum of ratios, which is not meaningful. As a result the Rule of 40 measure is also not meaningful.

We need, instead, the ratio of sums. For this, we set up the following advanced aggregators on the Dimension not displayed in the grid: Country.

For the Margin% Metric:

1. Select 𝝨 from the top toolbar. 
2. Select the Margin% Metric, opening the pane of aggregator options beneath.
3. Under OTHER DIMENSIONS > 1 non-Calendar Dimension, select Advanced aggregator from the drop-down.
4. In Calculation, select Growth.
5. Select Revenue Y0 for Metric A and Costs for Metric B.

 For the Revenue growth% Metric:

1. Follow the stepthrough above as far as step 4, selecting Revenue growth%.
2. In Calculation, select Growth.
3. Select Revenue Y0 for Metric A and Revenue Y-1 for Metric B.

For the Rule of 40 Metric:

1. Follow the stepthrough above as far as step 4, selecting the Rule of 40 Metric.
2. In Calculation, select Sum.
3. Select Margin% for Metric A and Revenue growth% for Metric B.

The aggregations now appear updated:

We still have blanks to fill, however, as the ratio totals to the right are not calculated:

We need more ratio of sums advanced aggregators, this time built on other advanced aggregations:

1. Select 𝝨 from the top toolbar. 
2. Select the Margin% Metric, opening the pane of aggregator options beneath.
3. Under DIMENSIONS IN GRID > Month, select Advanced aggregator from the drop-down.
4. In Calculation, select Growth.
5. Select Revenue Y0 for Metric A and Costs for Metric B.
6. Repeat for the Revenue growth% Metric, selecting Revenue Y0 for Metric A and Revenue Y-1 for Metric B.
7. Repeat for the Rule of 40 Metric, selecting Sum as the advanced aggregation type and Margin% and Revenue growth% as the Metrics to sum.

The Table is now fully updated:

Add ratios that reflect your selection dynamically

You can set up your Table so that ratios remain meaningful when you change the view on-the-fly using selectors:

1. In the Pivot menu, add the Country Dimension as a Row below your Metrics. The Pivot panel should look like this: 

   

    

2. In the 𝝨 menu, for Revenue Y-1, Revenue Y0 and Costs, select Sum aggregators in the Month and Country Dimensions.
3. For Margin% and Revenue Growth%, select Advanced aggregator > Growth as above.
4. For Rule of 40, select Advanced aggregator > Sum as above

Now when you change your selection of countries in the Country Page Selector, the Table updates the numerical and ratio data.

I hope these two examples help you streamline your reporting in Pigment. If you’ve found a creative way to use advanced aggregators in your own workspace, drop a note in the comments! I’d love to see what you're building.