Formulas overview

A formula is a child dimension member whose value is calculated from other dimension members rather than imported from your source system or published from your spreadsheet. Formulas take the hard work out of updating multiple spreadsheets by storing and updating frequently used calculations alongside the rest of your Cube data.

Add, remove, or update your formulas in the Formulas or Dimensions sections of the Cube Workspace 

Learn more about how Cube structures your data. 

Write Formulas

Create a formula by selecting a parent dimension member and then writing the formula in the Formula field. Use quotation marks around Cube dimension members to reference them. Get started with common formulas, or write your own by referencing our list of supported operations. 

The background color of a dimension in the formula field will turn green if the text you input matches a Cube dimension member. If the dimension name is mistyped, it will be red.

Choose which scenario(s) the formula should apply to if you need different calculations for different scenarios. See more on this below. 

Then select the formula rollup logic. This determines the calculation order:

Sum leaf values, then calculate

Calculate leaf values, then sum

See the guide to formulas rollup logic to learn more.

Example formula

We can add a formula to calculate Gross Margin in Cube by adding a new dimension member, naming it Gross Margin, and writing out the formula under Sources. 

We'll include this formula dimension member in our range in our spreadsheet. 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.

Scenario-specific formulas

Formulas can be general calculations that apply to all scenarios, such as Net Income, or they can be applied to specific scenarios, like a Forecast.

When working with accounts that typically have static values applied, use a formula for a specific scenario. For example, your Forecasted Revenue might be calculated by several drivers, while your Actual Revenue includes hard numbers from your accounting system.

In this example, you would create a Scenario-based formula for your Revenue Account that only applies to your Forecast Scenario.

Tip: Scenario-based formulas can only be used for Account dimensions and cannot be applied to Scenarios with Actuals write protection enabled. 

Explore an example of a Scenario-based formula to learn more. 

Make changes to dimension members used in formulas

Suppose you have a child dimension member 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 member, Cube automatically creates a "Revenue Only" dimension member 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. Cube also keeps 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:

1. 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.

2. 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.

3. 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 member, and will recalculate based on those changes. 

Recalculate a formula

Manual recalculation

Formulas can be manually recalculated at any time from your Workspace on the 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
• Re-mapping of dimensions
• Duplication of dimensions
• Disabling of dimensions
• Editing a Formula
• Changing a hierarchy
• Merging a scenario

Note: If your formula includes a formula-based dimension member, Cube will first recalculate the formula-based dimension members 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 (Revenue and COGS) must first be recalculated, followed by 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, and the formula automatically updates every time the corresponding values change in the relevant dimension members. 

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 if it works best for your process.

The best practice is to remove formulas for one-off calculations from your Cube when they are no longer needed. If you create these formulas repeatedly, add or leave them in your Cube.

Learn more about when to use formulas, tags, or dimension rollups.