Sign in

Optimize overview

Use optimize to find the driver inputs needed to reach a target KPI, like revenue, margin, or cash, and to make what-if analysis easier by recalculating the required input changes. It supports multivariate scenarios by adjusting multiple data input measures to meet a target value for a selected result measure.

Prerequisites#

  1. Set up the planning sheet with at least one data input measure or independent variable. Optimize adjusts the data input measure to achieve a target value or maximize or minimize the objective value. Learn more about data input measures here.

Native measures from the semantic model can't be edited. To use native measures in Optimize, create copies of the native measures before running optimization. In the Planning ribbon, go to Insert Column > Number. Select Copy from another series. Select the native measure to copy.

In the following example, Units Sold (Projection), COGS (Projection), and Revenue (Projection) are the data input measures.

  1. Add at least one formula measure (dependent variable) to the planning sheet. The formula measure acts as the target or output.

Ensure the formula uses a data input or forecast measure. Optimize adjusts the data input values to meet the target. The data input measure acts as the input or driver that Optimize changes to reach the target.

  • To insert a formula measure, in the Planning ribbon, go to Insert Column and select Formula. Enter a formula that uses the data input measures created in the previous step.
  • Set Row aggregation and Column aggregation to Formula and select Create.

The following screenshot shows the data input and formula measures.

Configure optimization thresholds#

Constraints are essential in optimization because they ensure the solution is realistic and feasible. Without constraints, the optimizer may produce mathematically correct results that cannot be implemented in the real world. Constraints ensure the solution satisfies business rules and operational limits, such as budget caps, production capacity, or minimum staffing requirements.

  • In manufacturing, in order to maximize profit, machine hours cannot exceed 500 hours, raw material is limited to 10,000 units, and production quantities cannot be negative.
  • In budget planning, to minimize operating costs, department budgets cannot fall below mandatory spending levels; total headcount must remain above a minimum.
  • In sales planning, to maximize revenue, sales discounts cannot exceed 20%; inventory availability limits the number of units sold.
Enterprise planning, Integrated with PowerTable and Intelligence, native to Microsoft Fabric. Co Engineered with Lumel.
BUILT ON