Use Optimize on derived measures and allocation rules

Optimization can also work on a formula measure that is itself derived from another formula measure. Optimize works backward through the measure dependencies and adjusts the same underlying input variables. The optimizer evaluates the complete formula chain and adjusts the underlying independent variables to achieve the specified target.

Use rules to control how Optimize adjusts underlying values while achieving a specified target. Apply locking rules to protect specific values, distribution rules to control how values are allocated, and min-max rules to enforce allowable value ranges during optimization. This provides greater control over the optimization process while preserving defined business constraints.

In this article, you learn to apply optimization to

• Derived measures that reference a formula
• Measures bound by allocation rules

Prerequisites#

Before you begin, review the Prerequisites section for Optimize to understand the initial setup requirements.

Run Optimize on derived measures#

The steps to configure Optimize and define constraints are the same as for target-based and direction-based optimization. The difference is that the calculated measure used for optimization can be derived from another calculated measure.

Example: Instead of optimizing Profit Forecast to reach a specific value, maximize Margin %. The optimizer can adjust Revenue Forecast, Advertising Forecast, Transport Forecast, and Purchase Forecast to find a combination that achieves the margin target.

1. Create the first-level formula that uses data input or forecast measures as underlying independent variables.

2. Create a dependent formula. In this example, you define Margin % as Profit Forecast / Revenue Forecast. Margin % therefore depends on the Profit Forecast formula, which in turn depends on the four underlying forecast measures.

3. Select a target cell in the dependent formula. Then, in the Planning ribbon, select Optimize.
4. Select the optimize objective. Notice that the Variables to Update are the forecast measures used in the first-level formula created in step 1.

5. Define thresholds for optimizing the independent measures. For more information, see Configure optimization thresholds.

6. Review the optimized values for the underlying independent variables and target measure. Then, select Apply to update the measures in the planning sheet with the optimized values.

7. Notice how Optimize updates the underlying Revenue Forecast, Purchase Forecast, Advertising Forecast, and Transport Forecast measures to achieve the target Margin %.

8. To convert the Margin % to a percentage value, select the measure and select the % icon from the Planning ribbon. Ensure Row Aggregation and Column Aggregation are set to Formula to optimize the measure further.

Apply rule-based optimization#

Define rules to control how Optimize modifies the underlying measures during optimization. Use different rule types to restrict edits, control value distribution, or enforce allowable value ranges.

• Locking rules restrict edits to specific rows, columns, or periods during optimization.
• Distribution rules control how values are allocated across measures and dimensions.
• Min-max rules enforce minimum and maximum values for measures during optimization.

For example, optimize Margin % while preventing changes to the North America Purchase Forecast. This allows Optimize to adjust other underlying variables while keeping specified values unchanged.

1. To create a rule, in the Model ribbon, select Rule, then select a rule type. In this example, you select Locking rule.

2. To lock edits to Purchase Forecast, set Apply to Measures to Selected Measures, then select Purchase Forecast from Choose Measures. To lock edits to the North America row category, set Row Selection to Custom and select North America from Custom Rows.

3. Follow the same steps outlined in the Run Optimize on derived measures section to configure Optimize. Note that the Purchase Forecast for North America is locked for editing.

4. The target value is achieved by optimizing the other values while preserving the values protected by the locking rule.

5. To convert the Margin % to a percentage value, select the measure and select the % icon from the Planning ribbon. Ensure Row Aggregation and Column Aggregation are set to Formula to optimize the measure further.