Multidimensional expense allocation
Data input cubes enable multidimensional planning by connecting planning sheets with different dimensional granularities, allowing data to flow between summarized and detailed plans. Instead of maintaining separate planning sheets and manually reconciling changes, data input cubes automatically distribute updates to detailed planning sheets based on allocation rules and aggregate changes back to higher-level plans.
Business plans are often created at a higher level of the organization but must be allocated to lower levels for detailed planning and analysis. In this article, learn how to use cubes to allocate OPEX from the country level to cities and products using multidimensional allocations.
Prerequisites
The column dimension is a standard date hierarchy (for example, year > quarter > month).
Configure a cube
When you create a data input measure, define one or more breakdown dimensions to convert it into a cube measure. The measure can then store and allocate values at multiple levels of dimensional granularity.
In the Planning ribbon, go to Insert Column > Number > Insert a new empty series. For more information, see Create a numeric data input measure.
Select +Add Breakdown from the Enable Multi-Dimensional Allocation section. This action opens the Add Breakdown window where you configure the cube.
To allocate values based on the weights of a driver measure, select the required measure from Reference measures. In this example, use Revenue Actual as the reference measure. To learn more, see weighted allocation.
The reference measure doesn't need to be part of the current planning sheet. You can select any measure from your semantic model as long as it doesn't have any null values for the breakdown dimensions. If the reference measure has null values for any of the breakdown dimensions, allocation fails.
Allocate plans, budgets, and forecasts across dimensions that aren't included in the current planning sheet by adding breakdowns. The row dimensions in the current planning sheet are automatically added in the first breakdown- in this example, Region_Name and Country_Name. Select additional dimensions, such as City_Name, Channel_Name, and Product_Name, to distribute values at a finer level of granularity.
Cubes supports planning across unrelated dimensions. For example, updates made to a revenue plan by product, geography, and channel can automatically flow to finance dimensions such as GL Account and Country, enabling seamless bidirectional allocations and enterprise-wide planning across business functions.
In this example, the product-level planning sheet also contains region dimensions, so we've added the product-level dimensions to the same breakdown. Select Add to create new dimension breakdowns as shown in the following image.
Create the breakdown and the measure.
After you create a cube measure, you can see it under Data > From Sheets > Cube.
Enter a value at the grand total level. In this example, we've copied the Operating Expense Actual value so that we can use the cube to allocate this value across additional dimensions. Plan automatically distributes the value equally among child rows and columns. Select the distribution icon to choose an alternate distribution method.
Create a calculated measure to compute Gross Margin at the region level.
This step is optional. It is not required to create cubes.
Import a cube measure into another planning sheet
After you configure a cube measure, you can use it in other planning sheets with different granularities based on the configured breakdowns. In this example:
You create a cube in a region-level plan.
You configure breakdowns on city, sales channel, and product dimensions.
You enter data for the cube measure.
You import the cube measure into the product-level plan.
The values you enter in the region-level planning sheet allocate to the product dimensions based on the weights of the reference measure. When you import the cube into the product-level planning sheet, plan automatically populates the entered values.
When you import a cube measure, 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, the cube breakdown is configured with the dimensions Region, Province, and City. The planning sheet can include any subset of these dimensions, such as Region > City or Province.
Go to Data > From Sheets > Cube. Select the measure to import. From More options (...), select Insert as measure. Alternatively, in the Model ribbon, go to Cube > Import Cube Measure and select the measure to import.
This action imports the cube measure into the product-level planning sheet. Organizations often plan values at higher levels of the business hierarchy and allocate them to lower levels for detailed planning. In this example, a cube allocates operating expenses (OPEX) from the region level to product-level dimensions.
Plan distributes cube measure values equally to breakdown dimensions such as city, sales channel, product family, and product. Select the distribution icon to choose an alternate distribution method.
Redistributing imported cube values affects only the unrelated dimensions and preserves existing allocations.
This action redistributes imported cube values across the product dimensions while preserving region-level allocations. The following screenshot demonstrates how the cube value remains unchanged for the Asia and China rows even after redistribution.
In the Planning ribbon, go to Show Columns and deselect the measures that are not required for planning - in this example, the native measure Operating Expense Actual.
Create a calculated measure to compute Gross Margin at the product level.
This step is optional. It is not required to create cubes.
Update cube values
Cubes support bi-directional updates between planning sheets of different granularities. In this example, update the Opex Allocation for China from 24.3m to 25m in the region-level planning sheet.
The cube allocates the updated value to the additional dimensions in the product-level planning sheet. Plan automatically recomputes calculated measures, such as Gross Margin, to reflect updates to cube values.
Similarly, the cube automatically aggregates updates made at a lower level of granularity and propagates the aggregated values to the planning sheet at the higher level of granularity.
Best practices for setting up a cube
Use a valid allocation driver: Always configure a single, clearly defined allocation driver (reference measure) such as prior year actuals, revenue, or units. Ensure the driver reflects real-world business weighting logic.
Allocate only across nonblank driver cells: Allocation only occurs where the selected driver measure has valid (nonblank) values. Avoid allocations across intersections where the driver is null, as it can cause allocation errors or unintended distributions.
Restrict input by using Allow Input – Based on Formula: To prevent users from entering or allocating values on invalid intersections, configure an input rule such as
[Driver Measure]!==BLANK.
By configuring Allow Input – Based on Formula, cells that don't satisfy the specified condition are automatically locked. This configuration ensures that users can allocate only at dimension intersections that meet the defined driver criteria (for example, where the reference measure is nonblank).
Last updated
Was this helpful?