Distribution rules

In addition to the distribution options available in the context menu at the total-level cells, you can formally set global distribution rules for specific rows and columns.

Let's start with an overview:

1. Apply Weighted Distribution Rule to Rows

You can specify how the row grand total or a specific subtotal is to be distributed across all rows.

Step 1: Select Measures

Choose the input and forecast measures to which the weighted distribution rule should apply.

Step 2: Select Row Dimensions

Specify the row dimensions that the rule will be applied to.

The unselected/unspecified measures, row dimensions, or column ranges within the 'Apply to' options will be allocated equally.

Step 3: Choose the Reference Measure for Weights

Select the source measure from which the weights will be derived. You can choose one of the following:

• A measure from the planning sheet

• A measure from an Infobridge query

• Manually configure the reference values

Step 4: Define the Reference Period Range

Select the period range to be used for calculating weights. Choose whether to:

• Use the weights from the selected range as is, or

• Use the average weight of that range.

Step 5: Limit the Rows (Optional)

Restrict the weighted distribution to specific row subtotals and their child rows, if needed.

When the rule is configured for only rows, the column grand total is distributed equally by default.

2. Apply Weighted Distribution Rule to Columns

You can specify how the column grand total is to be distributed across all columns.

Step 1: Select Measures

Choose the input and forecast measures to which the weighted column distribution rule should apply.

Step 2: Choose the Reference Measure for Weights

Select the source measure from which the weights will be derived. You can choose one from the following options:

• A measure from the planning sheet

• A measure from an Infobridge query

• Manually configure the reference values

Step 3: Select the Source Row for Weight

Choose the row from which the weight will be derived, or select Self to use the row’s existing weight.

Step 4: Define the Period Range

Select the period range to be used for calculating weights.

Step 5: Limit the Columns (Optional)

Restrict the weighted distribution to specific column periods or ranges, if needed.

The unselected/unspecified measures, row dimensions, or column ranges within the 'Apply to' options will be allocated equally.

3. Steps to configuring a rule

Let's now look at the steps to set a distribution rule in detail:

1. Click on the Distribution Rule from the Plan tab.

Distribution rule

1. Configure the details for the rule:

2.1. Rule Name: Enter a name for the rule.

2.2. Apply to Measures: Select the measures to which you want to apply the distribution rule. You can choose from all measures, selected measures, all visual columns, or selected visual columns.

Apply to measures

• All Measures: The distribution rule applies to all visual measures, including those already configured and those added later in the planning sheet. It covers both data input and forecast measures, whether they are visible or hidden, based on the selected rows and columns.

• All Visual Columns: The distribution rule is applied to all visual columns, including those already configured and those added later in the planning sheet.

• Selected Measures: The distribution rule is applied only to the selected measures.

  • Choose Measures: Select the measures to which the rule will be applied. We will apply the distribution rule to the 'Forecast' measure.

• Selected Visual Columns: The distribution rule is applied only to the selected columns.

  • Choose Columns: Select the columns to which the rule will be applied.

Choose Measures

Choose Columns

2.3. Rule Type: Here, you can change the rule to other types—locking or min-max rule—if required.

2.4. Apply Distribution Rule to: Select 'Rows' to set a distribution rule across row dimensions and rows; select 'Columns' to set a distribution rule across the period and column hierarchies.

4. Configuring Row Distribution

The following options are available when you want to apply the distribution rule to rows:

2.5. Apply to Dimensions: Select the row dimensions for which the total value should be distributed.

• All Dimensions: Distribute the total by weight across all dimensions.

• Selected Dimensions: Distribute the total by weight only to the selected dimensions. The unselected dimensions will have their values equally distributed.

Apply to Dimensions

2.6. Use Reference From: Select the measures from which the weights should be derived.

Use Reference from - options

• Measure: This option lets you select any measure to derive weights from.

  • Choose Measure: When you choose 'Measures', this drop-down displays a list of all visual measures (data input, formula, or forecast measure) available in the sheet. You can choose one from the list or use the 'Self' option to use the same measure's values as a reference to derive the weights.

  

  Choose the measure to derive the weight

  Infobridge Query: If the planning sheet is integrated with a bridge and you need to reference a measure from an Infobridge query, select this option. It should be noted that the Infobridge measure can be used to derive weight only if the rule is to be applied to a single row dimension (in the previous step).

You also need to select a query that contains a single row dimension. In the image below, we used the 'Revenue' measure as a reference to derive weights from Infobridge query 3, which has only one row dimension. This will apply to the 'category' dimension in all of the measures as chosen below:

Use measure from Infobridge query

• Manual Input: You can also input sample values from which the weights would be taken. Choose the Manual Input option and enter values in the editor window that opens.

You can observe that the editor allows you to enter values based on the chosen row dimensions.

All dimensions are selected

'Category' dimension is selected

• None: When you choose 'None', the values are distributed equally. Use this option for equal distribution.

Weights from Period: This option is applicable when you choose a measure to derive weights. Use this menu to specify from which period the weights should be taken for the chosen measure. You can choose preset options such as 'Current/Previous Year', 'Current/Previous Quarter' and so on.

You can also specify a custom period range using the 'Custom' option.

Weights from Period

Operation: You can use the periods' weights as is or use their average weight. When you select 'Custom', you can specify the period range along with the required operation.

Operation and custom range

Limit Weighted Distribution to Select Rows: Apply the weighted distribution rule to all rows or selected rows.

Rows Selection

• All Rows: Apply the distribution rule to all row levels in the selected dimensions and measures. This is ideal when you want to distribute the measure's grand total to all chosen row dimensions, by weight.

• Custom: Apply the distribution rule to specific row levels and their children by using the basic selection filter or advanced group filters. Other row levels are equally distributed. Use this to distribute a row's subtotal based on weight among its children.

Custom row total distribution

Selection: In the 'Selection' filter type, select the required rows. Double-clicking a parent row selects/unselects a row with all of its children. Click Submit.

Row Selection Filter

Filter: In this type, you can select the row dimensions using text conditions such as 'is', 'is not', 'starts with', 'ends with', etc.

Advanced Filter

• Click on Add filter to include more dimensions to filter.

• Choose AND or OR to logically combine multiple filter criteria.

• Use the Add group option to combine multiple groups of filter criteria.

4.1. Examples

Let's now look at a few examples for row distribution.

1. Distribution weights derived from the Revenue measure from the previous year (2024) are applied to all category dimension rows. The brand row values are then distributed equally.

Distributed weights applied only to category rows

1. The category row values are distributed equally (the unselected dimension); distribution weights derived from the Revenue measure from the previous year (2024) were applied only to all the brand rows thereafter.

Distribution weights applied to brand rows only

1. The distribution weights are applied to all dimensions in the example below (both Category and Brand). You can also choose 'All Dimensions' from the drop-down instead.

Distribution applied to all dimensions

5. Configuring Column Distribution

The following options are available when you want to apply the distribution rule to columns:

Use Reference From: Select the measures from which the weights should be derived.