Formulas take the hard work out of updating multiple spreadsheets by allowing values to be calculated in Cube based on the changes to data associated with your other dimensions.
In this overview:
What are Cube Formulas?
A Formula is a dimension whose value is calculated based on other Cube dimensions.
- Dimensions with one or more children (i.e., Parent Dimensions) cannot be Formulas.
- Formulas cannot include multiple Top Level Dimensions. (i.e., an Account and a Department).
Your data is stored at intersections of the lowest-level dimensions.
Formulas reference those intersections to calculate the value, monitoring the dimensions for changes and recalculating the formula based as they change - eliminating the need for manual updates.
Let's say we wanted to create a formula to calculate Gross Margin:
"Gross Margin" = "Revenue" - "COGS"Cube will check all values of "Revenue" and "COGS" on your behalf to be fetched into your spreadsheet as a dimension.
Learn how to create a Formula Dimension.
Considering Scenarios
Cube Formulas can be a general calculation that applies to all Scenarios (ex: Net Income) or applied to specific Scenarios (ex: Forecast).
Applying a formula to a specific Scenario is helpful when working with Accounts that typically have static values applied.
For example, your Forecasted Revenue might be calculated by several different drivers, while your Actuals Revenue are hard numbers coming from your accounting system.
In this example, you would create a Scenario-based formula for your Revenue Account that would only apply to your Forecast Scenario.
🗣️ Some things to keep in mind when writing Scenario Formulas:
- Scenario Formulas can only be used for Account dimensions
- Scenario Formulas cannot be applied to Scenarios with Actuals write protection.
- If you want to apply a formula to an Actuals scenario, be sure to make a copy of the scenario first.
Learn how to create Scenario-based Formulas.
Changing a Formula
Suppose you have a child dimension called Revenue that you would like to expand into the specific Revenue streams, e.g., Product Revenue.
When you add the new Product Revenue child dimension, Cube automatically creates a "Revenue Only" dimension to store all the data originally associated with Revenue. This is because values are stored at the lowest level.
So the hierarchy started like this:
- Revenue
Ultimately becomes:
-
Revenue
-
Product Revenue
-
Revenue Only
-
By default, Cube moves all the values in Revenue into Revenue Only. We also keep the formula consistent for Gross Margin, now using Revenue Only.
However, assume you add data and want to use Product Revenue for Gross Margin now.
-
Old formula: Gross Margin = "Revenue Only" - "COGS"
-
New formula: Gross Margin = "Product Revenue" - "COGS"
When you save the formula, Cube automatically notes and recalculates the change.
🗣️Depending on how much data you have, this can understandably take time for the recalculation to complete.
In some places, you might have had data for Revenue Only, but you still need to get data for Product Revenue or vice versa. When Cube calculates Gross Margin, it overwrites the old Gross Margin values with the new ones.
Let's take a look at some examples:
-
Before, your Cube had Revenue Only and COGS at this intersection. Cube also has Product Revenue at this intersection, so Cube recalculates and updates the value of the Gross Margin with the new formula.
-
Before, your Cube had COGS but no Revenue Only at this intersection, so Gross Margin was negative since Cube calculated "0 - COGS". Now that your Cube also has Product Revenue at this intersection, Cube overwrites the negative number with the newly calculated value.
-
Before, your Cube had Revenue Only and COGS at the intersection. Cube doesn't have new Product Revenue data to replace it, and Gross Margin will become negative here.
Cube automatically detects when a formula changes, whether it is the values themselves or a new dimension, and will recalculate based on those changes.
Recalculating Formulas
Formulas can be manually recalculated at any time from the Cube Portal on your Formulas page:
Once the recalculation has been completed, you'll be notified via email.
Formulas are automatically recalculated whenever a data change occurs for the dimensions that the formulas reference. The following events can trigger recalculations in your Cube:
- Data imports
- Data publish
- Remapping of dimensions
- Duplication of dimensions
- Disabling of dimensions
- Editing a Formula
- Changing a hierarchy
- Merging a scenario
🗣️ If your formula includes a formula-based dimension, Cube will first recalculate the formula-based dimensions before the formula can recalculate. Given the amount of data calculated, this can take time.
For example, Gross Profit is calculated based on value-based dimensions. As these values are updated, so are the relative dimensions (Revenue and COGS).
Then, a formula like Operating Income is calculated based on a formula-based dimension (Gross Profit) and a value-based dimension (Operating Expenses).
Operating Income = Gross Profit - Operating Expenses
When it comes time to recalculate, the formula's components must first be recalculated (Revenue and COGS), then the calculation for Gross Profit → Operating Expenses, and finally the output, Operating Income.
Writing Formulas
Data under any top-level dimension or from a specific child dimension can be used to create a formula. Except dimensions with Actuals write protection.
Put "quotation marks" around Cube dimensions to cite your parent dimension. See the table of operations supported.
The background color will turn green if the text you input matches a Cube dimension. If the dimension name is mistyped, it'll show red.
Using Formula Rollups
Formula rollup logic determines the calculation order:
- Rollup, then apply the formula using Sum leaf values, then calculate
- If this option is selected, the logic will first sum up the values stored at the lowest level for each dimension across the different Top Level Dimensions, then calculate your formula.
- Ex: Say you're calculating Gross Profit with the following formula:
"Revenue" - "COGS"Cube will first sum all values in "Revenue" across all your top-level dimensions. Then it'll sum all values in "COGS" across your top-level dimensions. Finally, Cube will calculate your formula by subtracting the total sums of Revenue - COGS.
- Apply the formula, then rollup, using Calculate leaf values, then sum
- If this option is selected, the rollup logic will first calculate the values in each quoted dimension within the different Top Level Dimensions (e.g., location/division/department/region). Then all the calculations (multiplications or divisions) will be summed.
- Ex: Say you're calculating Gross Margins, where your formula is
"Revenue" -"COGS"/"Revenue"in this case, Cube will first calculate each component across each TLD and then sum up the calculation outputs.
See Guide to Formulas Rollup Logic
Supported Operations
| Operation | Expression | Example | Recommended Rollup Logic |
| Add | + | "Revenue" + "Discounts" | Sum leaf values, then calculate |
| Subtract | - | "Revenue" - "COGS" | Sum leaf values, then calculate |
| Multiple | * | "Revenue"*3 | Sum leaf values, then calculate |
| Divide | / |
"COGS"/2 |
Sum leaf values, then calculate |
| Exponent | ^ | "Churn" ^2 | Sum leaf values, then calculate |
| Months Ago | <MONTHS_AGO("_",#months)> | <MONTHS_AGO("Principal",1)> | Calculate leaf values, then sum |
| Running Total | <RUNNING_TOTAL("_")> | <RUNNING_TOTAL("New ARR")> | Calculate leaf values, then sum |
| Year to Date | <YEAR_TO_DATE("_")> | <YEAR_TO_DATE("New ARR")> | Calculate leaf values, then sum |
When to use Cube Formulas vs. Spreadsheet Formulas
Cube Formulas are best used when you have a re-occurring formula in your spreadsheet that is based on varying calculations and/or rollups.
For example, suppose your standard reporting package includes Gross Profit and Operating Income. You can save time by having Cube calculate these on your behalf so that the formula automatically updates every time the corresponding values change in the relevant dimensions.
While Cube Formulas can also be used for one-off calculations, it's not recommended. These are best kept directly in your spreadsheet. However, you can still use them.
The best practice is to remove these from your Cube once it's no longer needed. If you create these formulas repeatedly, add or leave them in your Cube to give yourself some time back.
TL;DR
- A Formula Dimension is a dimension whose value is calculated based on other Cube dimensions.
- Scenario-based Formulas allow you to calculate formula values differently, depending on the Scenario dimension those values are associated with.
- Once created, formulas are recalculated as changes are made to the formula and/or dimension.
- Formulas use rollup logic to determine the order that calculations should run.
- Formulas are best used for calculations repeatedly used, like Net Income, Gross Profit or Margin, etc. One-off Formulas should be removed from your Cube after use.