In this article, you learn about the row properties and how to configure them in a model.
The Model Builder looks as shown below. There are two tabs: Closed Period and Open Period.
By default, the row configurations are the same for both closed and open periods when you start. Toggle between the tabs to specify different row configurations for the open and the closed periods.
Open period row values represent the plan, forecast, or any projection measures, whereas the closed period values refer to the actuals. Using this option, you can configure different formulas, data sources, or manual values for the actuals and the forecast.
To configure the row properties, select the Type, Configuration, and Aggregation dropdowns from the model builder view and choose the required options. Make sure you've chosen the correct period (open/closed).
Alternatively, hover over a row and select the pencil icon beside the row name to edit the complete row properties.
A side panel opens where you can configure the properties. It has General and Display tabs. Configure all row properties in the General tab and node display settings for the tree view in the Display tab.
Enter or edit the name of the row.
Assign the type of the row, which helps set up the required configuration. Select one of the row types below:
| Row type | Description |
|---|---|
| Formula | Row values are derived from the user-defined formula for the row. |
| Data Source | Row values are retrieved from the corresponding source data. |
| Data Input | Row allows users to manually enter the values. |
| Aggregate | Row aggregates values from child rows (for example, sum, product, average, etc.). |
| Driver Input | Row values are calculated based on the configured driver row, driver method, and driver inputs. |
| Distribution | Similar to the driver input method, but here you only specify the row total, which is distributed based on weights derived from the row itself or other rows or can also be equally distributed. You can also use it to copy row values from another row instead of a distribution function. |
| Linked Row | The row values, its properties, and simulation settings are linked to another row. This type can be configured for rows in the Open Period. |
Based on the above row types, fill in the configuration as required.
For a Formula row, enter the configuration as a formula. You can type the formula manually and/or select from the suggestions that appear as you start typing.
To access help suggestions with examples, press Ctrl+Space. To select between row references and functions, switch between the References and Functions tabs.
In this case, the row values are retrieved from the source data based on the row you select from the dropdown menu.
For Data Input nodes, you can either enter values manually or copy values from an existing row.
The example below demonstrates both options configured.
This type offers various aggregation options, allowing you to define how child rows have to be aggregated. Options include Sum, Subtract, Product, Divide, Minimum, Maximum, First, Last, Average and Average (Leaf Only).
In a Linked Row, you can select another row to link to. Linked rows share the same values, properties, configuration, and simulation behavior, ensuring they remain in sync. Use this type when the same row must be reused in multiple parts of the model, allowing all instances to be changed once. This type can be specified for open period rows.
If you choose the Driver Input type for a row, you can define how its values are calculated by selecting a driver row (the source row that influences it) and a driver method—such as addition, subtraction, multiplication, division, percentage of, or growth by.
You can also specify the aggregation and distribution methods for Driver Input rows.
Based on the inputs entered in the planning sheet, this row is automatically calculated using the selected driver configuration. To learn more, refer to this section: Create a driver-based model.
This row type allows you to specify how the entered row total value should be distributed—either equally, based on weights, or by copying values by trend
If you select to distribute by weight, specify the row from which the weights are derived.
If you select Copy, choose how values should be copied:
Enter the total in the planning sheet, and the values are distributed or copied accordingly based on the selected method.
Use the following options to control how values are displayed:
The following settings—Desired Trend and Simulation settings are for the tree view layout.
The desired trend for a row can be set to Increase or Decrease depending on the row context. For instance, the desired trend for the Revenue row is Increase whereas for the Expense row, it is Decrease.
This setting controls how node values are represented visually in the tree layout. When you simulate in the direction of the desired trend, the node is represented in green; when you simulate in the opposite direction, the node is represented in red.
Slide Right to: To increase the node value on sliding right, set the Slide Right to property as Positive Simulation and to decrease the value, set it as Negative Simulation.
Value: Set the maximum simulation range. The default simulation range is 100, so the simulation percentages are calculated as a percentage of 100.
Specify how the total row value should be aggregated from the period values.
The following are the available aggregations:
| Time aggregation | Description |
|---|---|
| Sum | Adds all the period values. |
| Minimum | Returns the minimum value among the period values. |
| Maximum | Returns the maximum value among the period values. |
| Average (children) | Calculates the average of the immediate child node values. |
| Average (leaf) | Calculates the average of the leaf node values. |
| Average excluding zeros | Calculates the average of the non-zero period values. |
| First | Displays the first period value of the node. |
| Last | Displays the last period value of the node. |
| Weighted average | Calculates the weighted average of the period values based on selected nodes. |
| Standard deviation | Calculates the standard deviation of the period values. |
| Formula | Calculates and applies a custom formula entered by the user. |
Distribute parent value to children: For Data Input rows, select this option to allocate the entered parent row value to child rows.
Include in total: When enabled, the row values are included in the parent total. It is enabled by default.
Bind for Cross filter/RLS: Enable Bind for Cross filter/RLS to ensure that cross-filter selections and row-level security (RLS) rules are applied to formula rows and data input rows that reference other rows. If the Bind for cross filter/RLS option is disabled, a manager responsible for Canada accounts may see a manually inserted row that references US data.
Minimum and Maximum values: For driver input and distribution type rows, you can specify the minimum and maximum permissible input values.
Description: Any text describing the row or technical notes can be entered in the Description box.
Create a model using Model Builder
