A formula is a child dimension whose value is calculated from other dimensions rather than imported from your source system. Formulas take the hard work out of updating multiple spreadsheets by storing and updating frequently used calculations in Cube alongside the rest of your data.
Cube stores values at intersections of dimensions. Formulas reference those intersections to calculate their values, monitor the dimensions for changes, and recalculate the formula as they change  which eliminates the need for manual updates.
Add, remove, or update your formulas in the Formulas or Dimensions sections of the Web portal.
Learn more about how Cube structures your data.
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 reference them.
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.
Get started with common formulas, or write your own by referencing our list of supported operations.
Using Formula Rollups
Formula rollup logic determines the calculation order:
 Sum leaf values, then calculate
 How it works: If this option is selected, the logic will first sum up the values stored at the lowest level for each dimension across the different toplevel dimensions, then calculate your formula.
 Example: 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 will sum all values in COGS across your toplevel dimensions. Finally, Cube will calculate your formula by subtracting the total sums of Revenue  COGS.
 Calculate leaf values, then sum
 How it works: If this option is selected, the rollup logic will first calculate the values in each quoted dimension within the different toplevel dimensions. Then, all the calculations (multiplications or divisions) will be summed.
 Example: Say you're calculating Gross Margins, where your formula is
"Revenue" "COGS"/"Revenue"
In this case, Cube will first calculate each component across each toplevel dimension and then sum up the calculation outputs.
See the guide to formulas rollup logic to learn more.
Example formula
We can add a formula in the Web portal to calculate Gross Margin in Cube by adding a new dimension, naming it Gross Margin, and adding the formula under Sources.
We'll include this formula dimension in our range in the Spreadsheet addon. When Cube fetches data, it will check all Gross Profit and Income values and calculate the formula in your spreadsheet.
Learn more about adding formulas.
Formulas for certain scenarios
Formulas can be general calculations that apply to all Scenarios, such as Net Income, or applied to specific Scenarios, like a 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 drivers, while your Actuals Revenue includes hard numbers from your accounting system.
In this example, you would create a Scenariobased formula for your Revenue Account that only applies to your Forecast Scenario.
Tip: Scenariobased formulas can only be used for Account dimensions and cannot be applied to Scenarios with Actuals write protection enabled.
Explore an example of a Scenariobased formula to learn more.
Changing dimensions used in formulas
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.
Note: It may take some 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
Manual recalculation
Formulas can be manually recalculated at any time from the Cube Portal on your Formulas page:
After the recalculation has been completed, you'll be notified via email.
Automatic recalculation
Formulas are automatically recalculated whenever a data change occurs for the dimensions that the formulas reference. The following events can trigger automatic 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
Note: If your formula includes a formulabased dimension, Cube will first recalculate the formulabased dimensions 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 less Operating Expenses, and finally the output, Operating Income.
Cube formulas vs. spreadsheet formulas
Cube formulas are best used when you have a recurring formula in your spreadsheet 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 if it works best for your process.
The best practice is to remove formulas for oneoff calculations from your Cube when they are no longer needed. If you create these formulas repeatedly, add or leave them in your Cube to give yourself some time back.