8.2 - Direction-based optimization
In this tutorial, you learn to use direction based optimization, define constraints, and apply optimized values to your planning sheet.
Prerequisites
A planning sheet is created with measures, row, and column dimensions assigned.
In this example, PY Profit is the difference between PY Revenue, PY Purchase, and PY Transportation. These measures are reported prior year actuals from the semantic model.
Create data input measures (Independent variables). Native measures from the semantic model can't be edited. Before running optimization, create copies of the native measures that require optimization.
In the Planning ribbon, go to Insert Column > Number. Select Copy from another series. Select the native measure to copy, in this case, PY Revenue.
Enter the Revenue Plan as the measure name. Set Insert as to Visual Measure. Ensure Input type is Number, row and column aggregation types are Sum, and Distribute parent value to children is selected.
Select Create. In this example, the same steps are repeated to create Purchase Plan from PY Purchase, and Transportation Plan from PY Transportation.
Create a formula measure (Dependent variable). The formula measure is the dependent variable. The independent variables are optimized to achieve the target value assigned to the dependent variable.
In the Planning ribbon, go to Insert Column > Formula.
Enter the Profit Plan as the measure name and enter the formula. Set Insert as to Visual Measure. Ensure the input type is Number. Row and column aggregation types must be set to Formula.
Select Create.
Manage Column Display
To view all the measures in the planning sheet, in the Planning ribbon, go to Show Columns and select the settings icon.
To focus on the measures required for optimize, deselect the native measures from the semantic model. In this example, the visual measures are Profit Plan, Revenue Plan, Purchase Plan, and Transportation Plan. Only the selected measures are displayed in the planning sheet.
Configure Aggregation Settings. Select the aggregation icon(Σ). Set Row Grand Total and Row Subtotal to Top.
Configure optimizer (Direction-based)
In this previous section, Optimize is used to achieve a specific target value. In this section, the steps outline how to achieve the maximum value for Profit Plan within specified constraints.
Select the cell that shows the total Profit Plan.
Select Optimize.
Set Objective to Target, then select the independent variables, that is, the data input measures (Revenue Plan, Purchase Plan, and Transportation Plan) in the Variables to Update section. Select Next.
Select Add Constraint to define thresholds for the independent variables. Refer to Add constraints to know more about configuring thresholds.
Select Run to start the Optimizer.
The Target Value of 55m is achieved by simulating values of Revenue Plan, Purchase Plan, and Transportation Plan (the independent variables). Review the simulated values.
If the target value isn't achieved, users can change the following parameters and rerun the Optimizer.
Strategy: (from 1 to 10) defines the size of adjustments made while trying to achieve the target.
Tolerance %: (from 0.1 to 20) is the acceptable deviation between the target value and the optimized value.
Number of iterations: (from 1,000 to 10,000) refers to the total number of times the Optimizer updates its internal mathematical model to move closer to the target value by changing the values of the independent variables.
Select Apply to replace the values of the data input measures with the optimized values.
Last updated
Was this helpful?