Inverse Formulas

By default, planning models do not support planning operations for cell values that are calculated by formulas. If you want to allow data entry for a formula, you can add one or more inverse functions to specify how the formula should be reversed.

For example, consider an account dimension that has a Profit member with the following calculation:

[Revenue] – [Cost]

With this formula, users cannot enter data for the Profit account until you define an inverse formula for it:

[Revenue] – [Cost] | INVERSE([Cost] := [Revenue] – [Profit])

Users can now can enter values for profit, and cost will be adjusted while revenue remains constant.

Note

Use the following operators to construct inverse functions:

|

A vertical bar marks the end of the base formula and the beginning of one or more inverse functions.
:=

Add a colon before the equals sign in inverse functions.

Note

Inverse formulas work for classic account models, and for both measures and accounts in a model with measures.
You can't change inverse formula values using a data action.
The target cells for an inverse formula can be booked or unbooked. However, unbooked cells are treated as 0 value in the inverse formula.

For example, consider a Gross Sales measure with the following formula:

[Price]*[Units Sold] | INVERSE([Units Sold] := [Gross Sales]/[Price])

In this case, you can’t enter data to a Gross Sales cell when the corresponding Price cell is unbooked. The resulting Units Sold value would be [Gross Sales]/0.

As you begin to type the inverse formula, the hint list suggests complete inverse formulas that you can select. To add multiple inverse formulas in this way, type or INVERSE at the end of the formula and then select the next inverse formula from the hint list.

If you need to create an inverse formula that is only applied under certain conditions, see Conditional Inverse Formulas.

Multiple Inverse Formulas

In some cases, it may not be possible to change the value for cost. For example, a cell lock may be applied to cost, or values may be entered for cost and profit simultaneously. To allow data entry in these cases, you can define a secondary inverse formula that assigns value to revenue:

[Revenue] – [Cost] | INVERSE([Cost] := [Revenue] – [Profit]) OR INVERSE([Revenue] := [Profit] + [Cost])

When the first inverse formula cannot be applied, the data entry can be carried out using the secondary formula. As a result, the revenue value is adjusted.

For multiple inverse functions, the priority is determined by the order in which they are typed.

Nested inverse formulas

Inverse formulas can assign value to another member or measure calculated by a formula that includes inverse functions. For example, after you add an inverse function to the profit formula, you can define an inverse function for an account that is calculated based on profit, such as profit margin:

[Profit] / [Revenue] | INVERSE([Profit] := [Profit Margin] * [Revenue])

Inverse formulas can also assign values to aggregated accounts, including accounts that use exception aggregation.

Inverse Formulas Across Different Versions

Inverse formulas can use data from a specific version, calculated using the Restrict or Lookup functions, as an operand or as the target of the inverse formula.

For example, you may have formulas that calculate the difference between revenue for the current version and the actual revenue. First, ActualRevenue is calculated by the following formula:

LOOKUP([Revenue], [d/Version] = “public.Actual”, [d/Version])

To enable the data entry on the difference, you can add an inverse formula to the calculation of RevenueVsActual:

[Revenue] - [ActualRevenue] | Inverse([Revenue] := [RevenueVsActual] + [ActualRevenue])

For more information, see Restrict and Lookup.

Inverse Formulas with Dynamic Time Filters

Inverse formulas can assign value to calculations that use dynamic time navigation, and they can also use such calculations as operands.

For example, consider the following calculation for IncomeStatementPreviousQuarter:

RESTRICT([IncomeStatement],[d/Date] = Previous("Quarter",1))

Data entry is already enabled for this restricted measure. However, you also want to allow planning users to simulate different values for Quarter Over Quarter Growth (QQGrowth), which is calculated based on IncomeStatementPreviousQuarter:

([IncomeStatement] - [IncomeStatementPreviousQuarter]) / [IncomeStatementPreviousQuarter] | INVERSE([IncomeStatement] := [QQChange] * [IncomeStatementPreviousQuarter] + [IncomeStatementPreviousQuarter])

For more information about dynamic time navigation, see Restrict and Lookup.

Inverse Formulas with Exception Aggregation

When you create an inverse formula for an account that uses exception aggregation, you can enter data on leaf members of the exception aggregation dimension.

For example, consider the following formula for a revenue account that uses sum exception aggregation along the Product dimension:

[PRICE]*[VOLUME] | INVERSE([VOLUME] := [REVENUE]/[PRICE])

In this case, data entry is possible on leaf members of the product dimension for this account, but not on parent members of the product dimension.

Data entry is also supported when the account data is filtered to a single leaf member of the exception aggregation dimension. If the account uses multiple exception aggregation dimensions, each of them must be filtered or drilled down to a single leaf member in order to perform data entry.

For more information about exception aggregation in revenue calculations, see Price * Volume Formulas.

Data entry on parent members of the exception aggregation dimension is possible if the exception aggregation type is first or last:

For first aggregation, you can enter data on a parent member if its first leaf member is already booked.
For last aggregation, you can enter data on a parent member if its last leaf member is already booked.

In these cases, the values that you enter are booked to the parent member's first or last leaf member, respectively.

You can also use exception aggregation accounts as operands in other inverse formulas.

Note

This does not apply to the exception aggregation types COUNT; COUNT excl. NULL; or COUNT excl. 0, NULL. Inverse formulas do not support accounts that use these aggregation types.

Inverse Formulas for Grand Totals

Inverse formulas can be added to accounts calculated using the GrandTotal and %GrandTotal functions.

For example, the following inverse function can be defined for a TotalRevenue account:

GrandTotal[Revenue] | INVERSE(GrandTotal[Revenue] := [TotalRevenue])

Data entry on TotalRevenue is booked to the aggregated value of the revenue account.

Restrictions

Inverse functions are not supported in the following cases:

The inverse function assigns value to more than one operand. For example: INVERSE([Revenue] := 1.5*[Profit], [Cost] := 0.5*[Profit])
The base formula uses a Link or ResultLookup function, or the inverse function uses an operand that is defined by a ResultLookup function. Inverse functions can be applied to formulas that use Restrict or Lookup functions, including Restrict and Lookup functions that use dynamic time navigation.
The inverse function uses specific cell values as inputs, instead of members.