Multidimensional forecasting

In enterprise planning, high-level targets (such as revenue or budget) must be distributed across complex business structures—like regions, product lines, departments, and time periods. Multidimensional or cube forecasting automates this process by linking data across different levels of detail into a unified model. Learn more about creating forecasts.

Instead of manually maintaining separate planning sheets for each dimension, cube forecasting enables real-time distribution and aggregation across multiple dimensions simultaneously.

In this article, you learn to allocate a revenue target forecast defined at the region level to product and sales channel dimensions.

Prerequisites

• The column dimension is a standard date hierarchy (for example, year > quarter > month).

Configure a forecast cube

When you create a forecast, define one or more breakdown dimensions to convert it into a forecast cube. The cube can then store and allocate forecast values at multiple levels of dimensional granularity.

In this example,

• You create a planning sheet at the region level

• You create a profit forecast cube and configure dimensional breakdowns

• You import the forecast cube into a product-level planning sheet

• You update the forecast in the product-level planning sheet and use bidirectional updates in cube to aggregate it to the region-level planning sheet.

1. In the Model ribbon, select Forecast, enter the forecast measure name, and set the forecast period.

1. Enable the Multi-Dimension Allocation toggle. Select the driver measure from Reference Measures. Driver measure weights allocate forecasts across dimensions. Learn more about weighted allocations in cube measures.

Reference any measure in the semantic model as the weighting measure for allocation. The measure does not need to be included in the current planning sheet.

Ensure the reference measure contains values for the forecast period in the current planning sheet. Otherwise, cube creation fails during allocation.

In this example, Previous Year Units is used as the reference measure. Although it's not assigned to the current planning sheet, you can still use it to determine the allocation weights.

1. A breakdown defines how a forecast is allocated across different dimension hierarchies (such as Region > Country > Sales Channel > Product Line) using a reference measure for proportional weighting. The row dimensions assigned to the planning sheet are automatically treated as the first breakdown.

The top-level row dimension in the current planning sheet is required to create breakdowns for dimensions that are not included in the sheet. In this example, Region_name.

To allocate values across additional dimensions, select + Add to create a breakdown. Define breakdown dimensions.

1. Select Next. Closed forecasts for previous years are static, and you can't edit them. Configure the measure or formula to populate closed forecasts.

1. In this example, to create a zero-based forecast, set the Open Periods configuration to Data Input and select Save.

1. After you create the forecast cube, you can see it in the From Sheets section of the Data pane.

1. Enable Column Subtotal in the Planning ribbon and enter the forecast total value for 2026. Plan automatically distributes the value equally among child rows and columns. Select the distribution icon to choose an alternate distribution method.

In the following steps, you use bidirectional cube updates to modify the forecast in a different planning sheet at a different granularity. The cube automatically aggregates the updates and propagates them back to the Global Target Revenue planning sheet.

Import a forecast cube into a different planning sheet

After configuring a cube measure, it can be used in other planning based on the configured breakdowns. For instance, a cube created in a region-level plan can be imported into a product-level plan.

1. Create a second planning sheet and assign measures and dimensions.

When importing a forecast cube, the column dimensions in the second sheet must match those of the original sheet.

The row dimensions in the planning sheet must be a subset of the dimensions configured in the cube breakdowns. The dimensions can be in any order.

For example, if the cube breakdown is configured with the dimensions Region, City, Channel, Product Line, the planning sheet can include any subset of these dimensions, such as Channel > Product Line or Region > City > Product Line.

In this example, the original planning sheet uses Year > Quarter > Month as the column hierarchy, so the second planning sheet uses the same column dimensions.

One of the breakdowns is Region > Channel > Product Family > Product. The second planning sheet uses Channel, Product Family, Product as the row dimensions.

1. Go to Data > From Sheets > Cube. Select the cube measure to import. From More options (…), select Insert as measure. This action imports the forecast cube measure.

1. Plan distributes forecast values equally to breakdown dimensions such as sales channel, product family, and product. Select the distribution icon to choose an alternate distribution method.

1. Cubes support bi-directional updates between planning sheets of different granularities. Update a value in the cube measure in the second planning sheet. In this example, add a 10% increase to the Target Revenue Forecast at the channel level.

1. The cube aggregates the updated value and propagates it to the region-level planning sheet. The new forecast value overwrites the forecast entered in step 7. In the same way, any updates made to the original planning sheet cascade to child sheets.