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?
 How Formulas Work
 Writing Formulas
 When to use Cube Formulas vs. Spreadsheet Formulas
 TL;DR
What are Cube Formulas?
Rather than pulling in values and formulas from various spreadsheets, use Cube Formulas to look up these values using your Cube Dimensions to calculate for you.
Cube Formulas can be used to inform specific scenarios (ex: Forecast) or create a dimension based upon a calculation (ex: Net Revenue).
Dimension Formulas
A Dimension 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).
Learn how to create Dimensions with Formulas
Scenario Formulas
Scenario formulas allow you to calculate formula values differently, depending on the Scenario dimension those values are associated with.
🗣️ Somethings to keep in mind when writing Scenario Formulas:
 Scenario Formulas can only be used for Account dimensions and is helpful if you want to apply them to a specific scenario rather than across All Scenarios (the default).
 Scenario Formulas cannot be applied to Actuals due to write protection.
 If you want to apply a formula to
For example, if you want to Forecast an account using a formula but want the Actuals data direct from your General Ledger.
Learn how to create Scenariobased Formulas.
When writing a formula, you have the option + Add Scenario Override. This allows you to split up multiple formulas across a dimension. Multiple formulas per dimension allow Cube to split the expected data source across scenarios.
For instance, your Actuals data for Revenue might be hard data direct from your general ledger. However, your Forecast data for Revenue might be a formula that references other drivers.
For example:

For
Actuals
&Budget
scenarios:
Formula 1:
Gross Margin = Revenue  COGS


For
Forecast
scenario, maybe you want to do something like more indepth:
Formula 2:
Gross Margin = MONTHS_AGO(Revenue, 12) * 1.5  MONTHS_AGO(COGS, 12) * 1.3

Formula 1 will apply to two scenarios:

10 Departments * 24 Times * 2 Scenarios = 480 intersections
Formula 2 will apply for one scenario:

10 Departments * 24 Times * 1 Scenario = 240 intersections
Then, when Cube calculates the values for Gross Margin, if the scenario is Actuals
or Budget
Cube will use Formula 1. If it’s Forecast
, we’ll use Formula 2.
How Formulas Work
On the backend of Cube, your data is stored in an OLAP (Online Analytical Processing) Cube, where values live at the intersection of the lowestlevel dimensions.
Values aren't altered directly but are monitored for changes made to their related dimensions and then recalculated based on the changes.
Each dimension may have hundreds or thousands of possible values in the Cube.
For example, the dimension "Revenue" has potential values at every possible combination of:

Department ("Marketing Revenue," "Sales Revenue," "Admin Revenue," etc.)

Scenario ("Actuals Revenue," "Budget Revenue," "Forecast Revenue," etc.)

Time ("January Revenue," "February Revenue," etc.)

Optional  Custom Label

Ex: Location ("New York Revenue," "Los Angeles Revenue," etc.)

Suppose Sample Co has 10 Departments, two years of data (24 months), and 3 Scenarios. In that case, there are 720 possible "Revenue" values, not including rollups (e.g., "Q1", "All Departments", etc.), at the intersection of all possible dimension combinations.
10 x 24 x 3 = 720
Using formulas removes the need to update various functions throughout your spreadsheet and allows Cube to do the heavy lifting for you.
Let's say we wanted to create a formula to calculate Gross Margin:
"Gross Margin" = "Revenue"  "COGS"
Continuing with our Sample Co from above, Cube will check all 720 values of "Revenue" on your behalf. In addition, "COGS" also has 720 possible values, so Cube checks them as well.
That means that the "Gross Margin" has 720 possible values, each of which is the formula "RevenueCOGS" at the intersection of other dimensions. To calculate those 720 values, Cube checks the 1,440 intersections for Revenue & COGS in the Cube. 😮💨
… and so on for all 720 possible dimension intersections. Do you love us yet?
Incomplete Data
While there are 720 possible locations for "Revenue" and "COGS," not all locations will necessarily have data.
For example, "ForecastJan19HRRevenue" probably doesn't have data  why would we be forecasting the past?
Ultimately there are four possibilities:

Cube has Revenue and COGS for the given intersection of other dimensions, so it calculates Gross Margin as expected.
Revenue  COGS = Gross Margin

Cube has Revenue but not COGS at the given intersection. In this case, Cube assumes COGS is zero.
Revenue  0 = Gross Margin

Cube has COGS but not Revenue. Assume Revenue is zero like above. Gross Margin will end up being a negative number.
0  COGS = Gross Margin

Cube has neither COGS nor Revenue. So the set value of the intersection is "None" (basically zero, but specifically meaning "we have no data for this, but we did try to calculate it")
Changing a Formula
Suppose you add a new revenue stream beneath Revenue (a parent dimension) called Product Revenue (a child dimension). When you add the child dimension, Cube automatically creates an "Only" dimension alongside it. This is because we only store values and calculate formulas at the deepest level.
The hierarchy updates to:

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. This means repeating reviewing all 720 intersections for Product Revenue and all 720 intersections for COGS.
🗣️Depending on how much data you have, this can understandably take time to update for recalculation.
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 if any of the following changes occur to the dimensions used in a formula:
 Data imports
 Data publish
 Remapping of dimensions
 Duplication of dimensions
 Dimension is disabled
 Formula is edited directly
 The hierarchy changes
🗣️ If your formula includes a formulabased dimension, the formulabased dimension must first recalculate its relevant values before the formula can recalculate. Given the amount of data calculated, this can take time.
For example, Gross Profit is calculated based on valuebased 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 formulabased dimension (Gross Profit) and a valuebased 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 toplevel 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 toplevel dimensions. Then it'll sum all values in "COGS" across your toplevel 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 reoccurring 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 oneoff calculations, it's not recommended. These are best kept directly in your spreadsheet. However, you can still use them. 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 Dimension Formula is a dimension whose value is calculated based on other Cube dimensions. Learn how to create a Dimension Formula.
 Scenario Formulas allow you to calculate formula values differently, depending on the Scenario dimension those values are associated with. Learn how to create a Scenariobased Formula.
 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. Oneoff Formulas should be removed from your Cube after use.
Comments
0 comments
Please sign in to leave a comment.