You may need to use a formula for an account in your analysis, but only for a specific scenario you're working with. You can do this by creating scenario-based formulas.
This feature is helpful if you want to Forecast using a formula but still want Actuals data coming from a general ledger. For example:
-
Actuals and Budget scenarios:
"Revenue" - "Cost of Goods Sold" -
Forecast scenario:
<MONTHS_AGO("Revenue", 12)> * 1.5 - <MONTHS_AGO("Cost of Goods Sold", 12)> * 1.3
Before you start: other formulas can't reference a scenario-based formula. If Gross Margin, EBITDA, or any other formula needs to read this account, use an IF condition on Scenario instead. See Choose between a scenario-based formula and IF.
Create a scenario-based formula
The steps to create a scenario-based formula are nearly the same as adding any other formula to Cube, except that you'll apply this formula to a specific scenario. Scenario-based formulas work on Account members.
- On your Formulas page, click on the +Formula button.
- In the window that opens, name the formula and select where this formula dimension member should sit in your hierarchy.
- Write your formula using quotation marks around the names of Cube dimension members, or select the dimension member name from the suggestions. See a list of common formulas and supported operations.
- In the Apply To dropdown menu, select the scenario you'd like this formula to be associated with.
-
After you select one scenario, you can select additional scenarios using the +Add scenario override option that appears next to Formula. Use scenario overrides to apply variations of a formula to different scenarios or apply a formula to specific scenarios.
Note: Formulas cannot be applied to the actuals scenario or any scenario with write protection enabled.
- Apply rollup logic and complete any optional fields.
- Click Save Formula to save your changes. Your formula will appear in the list on the Formulas page.
Choose between a scenario-based formula and IF
There are three ways to calculate an account differently by scenario. The main difference is whether other formulas can reference the result.
| Approach | How | Can other formulas reference it? | Best for |
|---|---|---|---|
| Scenario-based formula | Apply To a scenario, plus scenario overrides | No | Accounts nothing else depends on, such as a final reporting line |
| IF on Scenario | IF("Scenario" = "Actuals", "Revenue" / "Customer Count", SKIP) |
Yes | Drivers and any account other formulas use, such as revenue or ARPU |
| Criteria on Scenario | "Revenue" - "Revenue"("Scenario": "Budget") |
Yes | Comparing scenarios or reading one scenario from another |
If you try to reference a scenario-based formula, Cube shows
Scenario based formulas cannot be referenced in other formulas and
names the formula to remove. Rebuild it with IF on Scenario, and use
SKIP
for the scenarios that should be entered by hand or imported.
- Write Conditional Formulas With IF
- Mix Calculated and Manual Values With SKIP
- Add Criteria to a Formula Reference
Choosing a scenario when a formula reads a Table
Tables can have a separate version saved per scenario, so a formula that reads a Table needs to know which version to use. This is controlled in the Table reference itself rather than in the formula's scenario settings.
-
TABLE_LOOKUP("Product Breakdown", …)follows whichever scenario the cube is evaluating. A Forecast cell reads the Forecast version of the Table. -
TABLE_LOOKUP("Product Breakdown[Actuals]", …)always reads the Actuals version, whatever scenario is being evaluated. Use this when the value should be the same across scenarios, as a bonus policy or an FX rate usually is.
Note: naming a scenario also means Cube stops matching on scenario, which can change how many rows match. If you name a scenario the Table has no version for, the formula will not save.
See Reference Table data in a formula.
Related articles
- Formulas overview
- Use the Formula Editor
- Write Conditional Formulas With IF
- Guide to formulas rollup logic