Configure row properties in the model
In this article, you learn about the row properties and how to configure them in a model.
Hybrid Row Configuration
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.
Row Properties
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.
Row name
Enter or edit the name of the row.
Type
Assign the type of the row, which helps set up the required configuration. Select one of the row types below:
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.
Configuration
Based on the above row types, fill in the configuration as required.
Formula
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.
Note
To access help suggestions with examples, press Ctrl+Space. To select between row references and functions, switch between the References and Functions tabs.
Data Source
In this case, the row values are retrieved from the source data based on the row you select from the dropdown menu.
Data Input
For Data Input nodes, you can either enter values manually or copy values from an existing row.
Row: Select a data source row whose values you want to copy.
Static: Enter a value that will be applied across all periods.
The example below demonstrates both options configured.
Aggregate
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).
Linked Row
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.
Driver Input
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.
Distribution
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:
Copy without trend
Copy with trend by value
Copy with trend by percentage, and specify the trend values
Enter the total in the planning sheet, and the values are distributed or copied accordingly based on the selected method.
Formatting
Use the following options to control how values are displayed:
Scale: Choose a number scaling option, such as None, Auto, Thousands, Millions, Billions, or Trillions.
Decimal Points: Specify the number of decimal places to display for numeric values.
Prefix: Add a prefix to values, such as a currency symbol.
Suffix: Add a suffix to values, such as units.
Desired Trend
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.
Simulation
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.
Aggregation
Specify how the total row value should be aggregated from the period values.
The following are the available aggregations:
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.
Other Options
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.
Next step
Create a model using Model Builder
Related content
Last updated
Was this helpful?