Add Criteria to a Formula Reference – Cube

Add criteria to any reference in a formula to read its value from a specific department, market, scenario, or other dimension member, instead of from the cell being calculated.

Required permission: Adding criteria uses the same permissions as creating and editing any formula in Cube. No additional permission is needed.

How criteria work

A plain reference like "Revenue" reads Revenue at the same department, entity, scenario, and month as the cell being calculated. Criteria change one or more of those and leave the rest alone.

"Commission Rate"("Market": "Market - Other")

This reads Commission Rate at the Market - Other member. Every dimension you don't name still follows the cell being calculated, so a Sales cell in Mar-26 reads the Mar-26 Commission Rate for Sales at Market - Other.

Part	What it does
`"Commission Rate"`	The dimension member you want a value from.
`("Market": "Market - Other")`	The criteria. Each one names a dimension and one of its members, separated by a colon.

You can add several criteria to the same reference, separated by commas. Each dimension can appear only once.

"Headcount"("Department": "Engineering", "Entity": "GCo US")

Note: Wrap both the dimension and the member in straight double quotes. "Revenue"("Department": "Sales") saves. "Revenue"(Department: Sales) does not.

Add criteria in the formula editor

Go to Formulas in your Workspace.
Select New, or edit an existing formula.
Place your cursor in the Formula field where the reference should go.
Select Insert Dimension.

For a full tour of the editor, see Use the Formula Editor.

Select a Dimension and Member for the value you want.
Select a dimension from the list below Member to add it as a criterion.
Select a member for that dimension.
Repeat steps 6 and 7 for each additional criterion.

Note: Insert into Formula stays disabled until every criterion has a member. To drop a criterion, select the remove icon next to it.

Check the reference in Preview (will insert).
Select Insert into Formula.
Finish the formula and select Save Formula.

Tip: You can also type criteria directly. After a quoted reference, type ( and the editor suggests dimensions. After the colon, it suggests members of that dimension. Suggestions only include members you have access to.

Examples

Apply one rate everywhere

Your standard commission rate is maintained once, at Market - Other, but bookings come in across every market. On a Sales Commission at Standard Rate account:

"Bookings" * "Commission Rate"("Market": "Market - Other")

Each market's bookings are multiplied by the Market - Other rate. Department, entity, scenario, and month still follow the cell, so a department with its own rate at Market - Other uses that rate.

On a Revenue Share by Entity account:

"Revenue" / "Revenue"("Entity": "Global Co")

The first reference reads each entity's own revenue. The second always reads the Global Co total.

Entity	Revenue	Revenue at Global Co	Revenue Share
GCo US	6,269,142	9,397,785	66.7%
GCo UK	1,429,959	9,397,785	15.2%
GCo Australia	1,698,684	9,397,785	18.1%
Global Co	9,397,785	9,397,785	100%

Compare against another scenario

On a Variance to Budget account:

"Revenue" - "Revenue"("Scenario": "Budget")

In Forecast, this returns Forecast minus Budget. In Actuals, it returns Actuals minus Budget. One formula works in every scenario you view it in.

Tip: A formula that reads another scenario through criteria applies to all scenarios, so other formulas can reference it. A scenario-based formula can't be referenced by other formulas.

Allocate a shared cost

Rent is booked to G&A and should be spread across departments by headcount. On an Allocated Rent account:

"614000 - Rent Expense"("Department": "General & Administrative") * "Headcount" / "Headcount"("Department": "All Departments")

Each department gets the G&A rent multiplied by its share of total headcount. The allocations add back up to the full rent at All Departments.

Rules and behavior

Dimensions you don't name follow the cell. Only the dimensions listed in the criteria change.
A parent member returns its rolled-up value.
("Department": "All Departments")
reads the total for every department underneath it.
An intersection with no data counts as empty. If nothing else in the formula has data for the cell, the cell shows blank. If another reference in the formula has data, the empty reference counts as 0. With Sum leaf values, then calculate, totals are calculated from rolled-up inputs, so an empty reference counts as 0 at parent members.
Criteria stack across formulas. If Formula A is
"Total Revenue"("Department": "Sales")
and Formula B is
"Formula A"("Scenario": "Budget")
, Formula B returns Budget revenue for Sales.
A formula can't reference itself, even at a different intersection.
"Revenue"("Scenario": "Budget")
can't be used inside the Revenue formula.
Tags can be used as criteria. If Platform Team is a Department tag,
"Operating Expenses"("Department": "Platform Team")
adds up Operating Expenses for every active member of the tag. Members with no data are skipped. Change who's in the tag and the formula follows on the next fetch.
Criteria don't filter. Criteria change where a reference reads its value from. They don't limit which cells the formula calculates in, so a formula with criteria returns a value in every cell. To calculate only for certain departments, entities, or other members, use an IF condition instead, for example
IF(ISCHILDOF("Sales + Marketing"), "Expense", 0)
. See Write Conditional Formulas With IF.
Criteria can't be added to tag formulas.

Note: When you stack criteria, don't name the same dimension in both formulas. Put both criteria in one formula instead.

Choosing rollup logic

Rollup logic decides whether Cube calculates at the lowest level and then adds the results, or adds up the inputs first and then calculates. Formulas with criteria follow the same rollup logic rules as any other formula.

If your formula	Use	Why
Multiplies by a rate that varies by department, market, or entity	Calculate leaf values, then sum	Each cell uses its own rate, then the results add up. Adding the rates first would multiply the total by a sum of rates.
Divides to produce a ratio or percentage	Sum leaf values, then calculate	A percentage can't be added up. The total is recalculated from the totals.
Reads a value from a fixed member or tag on its own, for example a company total or a tag total	Sum leaf values, then calculate	The value is read once at every level. With Calculate leaf values, then sum, it's added once for every member underneath, so parent totals come out too high.
Only adds or subtracts	Calculate leaf values, then sum	Both options return the same number, and this one fetches faster.

If something is not right

What happened	What to check
The formula won't save	Check that the dimension and member are both in straight double quotes, the member belongs to that dimension, and each dimension appears only once in the criteria.
Tag dimensions cannot be used in formulas	Tag references aren't available in your Cube yet. Contact Cube Support.
The value shows under every member in a drilldown, or totals are much higher than expected	Criteria are being used as a filter. Use an IF condition to choose which cells calculate. See Rules and behavior.
Scenario based formulas cannot be referenced in other formulas	The reference points at a scenario-based formula. Rebuild it as a formula that applies to all scenarios, using criteria or an IF condition on Scenario.
The result is blank or 0	Nothing is stored at that intersection. Confirm data exists for the member you named, in the scenario and month you're viewing.
Totals look too high or too low	Check the rollup logic. See Choosing rollup logic.

Related to