Avoid updating multiple spreadsheet formulas by automating standard calculations and storing them in Cube as a formula dimension member. A formula references other dimension members and allows its value to be calculated whenever referenced values are updated.
For example, calculate Gross Profit by creating a formula in Cube that subtracts 'Cost of Goods Sold' from 'Revenue'
Add a new formula
1. Go to your Workspace and click Formulas or Dimensions from the navigation bar.
2. Click New.
3. Name your formula.
4. Select a Parent from the dropdown menu provided.
The formula's data will originate from the selected parent and can be a dimension, such as Account or Scenario, or a dimension member.
Note: Scenario dimensions with write protection enabled cannot be used in formulas.
Learn more about using formulas with scenarios.
6. Write your Formula using quotation marks around the dimension member names or select the populating name from the suggestion. See the table of supported operations.
The background color will turn green if the text matches a Cube dimension member. If the name is mistyped, it'll show in red.
7. By default, your new formula applies to All Scenarios. Select a specific scenario instead, or proceed to step 8.
Note: Formulas cannot be applied to the Actuals Scenario or any other scenario dimensions with write protection enabled. Learn more about write protection.
8. Determine your rollup logic. Rollup logic determines the calculation order:
- To sum up the components of each quoted dimension member and then perform the calculation, select Sum leaf values, then calculate
- To first calculate the values across quoted dimension members and then sum their parts, choose Calculate leaf values, then sum
Learn more about formula rollup logic.
9. (Optional) Select a rollup type: sum, average, ending balance, or beginning balance.
Learn more about rollup types.
10. Click Save Formula, and you will see the new formula in your dimensions or formulas list.
The menu icons on the Formulas page allow you to recalculate, edit, or clear a formula at any time.
If you want your formula in a particular location in your hierarchy, you can re-order the formula from your Dimensions page just like any other dimension member.