Include additional measures (for driver-based budgeting)
By default, in each budget worksheet row, there is only one measure, for example, sales. However, there might be times when you want to use additional measures. For example, you might want to use the quantity and price measures to calculate the sales value.
You can use additional measures to drive the budget data in three situations:
You define the additional measures by adding rows in the workbook or tab setup. The following row types are available:
Row type | Description |
|---|---|
| Input rows are initially populated with data from the selected stream, measure and offset (time period). This data can be edited during the budgeting process, then published to a measure in the underlying database. |
| Calculation rows are populated with the result of a formula that references other rows. The results can be published to a measure in the underlying database. |
| Working rows are blank rows that have no measure attached and are therefore, not published to the underlying database. Data can be entered into these rows during the budgeting process. |
| Reference rows are set up the same way as an Input row (populated with data from the stream) but the data is for reference purposes only, so it cannot be edited during the budgeting process. Reference rows are an alternative to Comparison rows. |
| Days rows are populated with the number of days in the budget period, according to the type of days you want select. This data is for reference purposes only, so it cannot be edited during the budgeting process. |
| Coming soon - Property rows are populated with data about one of your properties (static information stored about dimensions, such as a unit of measure or price list for a product), giving you a way to reference the properties in your database directly within your budget. As the data is for reference purposes only, it cannot be edited during the budgeting process. |
 | Lookup rows contain data from the Lookup tab. |
Configure the additional measures
In the setup screen:
There is one primary input row that corresponds to the measure you define in the previous step (screen) in the budget or tab creation process. You cannot edit the setup of this input row here; if you need to edit it, go back to that previous step and make your changes there.
The primary input row becomes the top-level row in the workbook, under which the additional rows (measures) sit.
You can convert the primary input row to a calculation row (see steps below).
You can add other rows as outlined below.
Add a row
Some rows have a Measure and/or Aggregation setting. The following information applies to these settings:
Measure
The measures that are available depend on the selected stream in the underlying database.
In Input and Calculation rows, you can only use each measure one time. So when you select a measure in one Input/Calculation row, it becomes unavailable for selection in subsequent Input/Calculation rows. It is possible to select the same measure in a Reference row because the data is not published back to the stream.
Some of the measures have a yellow calculator icon next to them, which signifies that they are a calculated measure in the underlying database. You can hover over the icon to view the underlying formula. These measures will not be published to that database for that reason. For example, in the image below, you might input the price when budgeting, however, this is a calculated measure (Local Value - Cost), therefore, it will NOT be published back to the underlying database.
Aggregation
This setting determines how the value is aggregated in the Total rows in the worksheet. You can select SUM, MIN, MAX, MODE, MEDIAN, FORMULA or NONE.
Some aggregation methods are more suitable than others, depending on the selected measure. For example, for a total, such as local value or quantity, SUM is the most suitable, whereas the FORMULA option is more suitable for margin %, as it calculates the value.
The FORMULA option is selected by default in Calculation rows and Input rows that use a calculated measure. If a red error displays, it means one or more of the formula elements are missing, so you need to either select an alternative aggregation option or add row(s) for the missing element(s). The image below illustrates the latter - the Local Cost measure was missing from the formula,. so another Input line was added for that measure.
See the Use formulas page to learn about the functions you can use in the calculations.
Add the other rows that you want to include in your calculation.
Click the blue Add button on the right side of the screen and select Calculation.
Enter a name for the calculation.
Enter the calculation formula:
Click in the calculation box to activate it, then click the header of one of the other rows to insert a reference to that row in the formula.
Enter mathematical symbols and reference to other rows, as required. See the following images and watch the video (see link in right panel) for further explanation.
Select the measure, if you want to publish the calculation result to the underlying database.
Change the default aggregation (FORMULA) method, if required.
Click the blue Add button on the right side of the screen and select Days.
Enter a name for the row.
Select the type of days you want to use:
Calendar Days: The number of days in each period in the budget. For example, if you have a monthly budget, you will get 30 days for November, 31 days for December and so on.
Week Days: The total count of the number of Monday through to Friday days in the budget period. For example, you will see 20 days in the months that have 4 weeks and 25 days for the months that have 5 weeks.
Working Days calendar: A selection of working days calendars which you can use to calculate the number of days. You administrator sets up the working days calendars for your organization.
Click the blue Add button on the right side of the screen and select Property.
Enter a name for the row.
Select the budget dimension, then select the property within that dimension.
Mark a row as driven (Database driver tabs only)
If you are adding rows in the setup of a Database driver tab, you have the option to configure which of the rows drive the corresponding categories in the Main tab of the budget workbook. Select the Driven toggle button as required.
If you do mark a row as driven, ensure you have selected the appropriate category on the first screen of the Driver setup.
Lock a row for editing by certain users (Input and Working rows only)
You have the option to lock individual Input and Working rows for editing. This action allows you to add a layer of security around budget values, as you can be sure that only the appropriate people can edit certain budget values. You can change this setting at any time, as your budget workflow progresses.
For example, in an Input row, you can allow the data, such as product price, to be initially input by the budget owner or pricing manager (administrator), then lock that row, so that budget contributors cannot edit the price data.
The following user roles are available for each measure row, in order of restricted to full access:
Budget owner can edit the values.
Budget owner or administrator can edit the values.
Contributors can edit the values (default).
Click the User button at the end of the row to switch between the different roles as required.
Reorder the rows
The order of the rows on this screen corresponds to the order in which the rows display in the corresponding budget worksheet.
Click and hold the Move button on the left side of the row (a blue background displays), then drag the row up or down to its new position.
Convert the primary input row to a calculation row
In the primary input row, click the blue Input button. To convert back to input format, click the button again.
See the Add a calculation row section above for information on how to enter formulas.
View the additional measures rows in the worksheet
When you exit the setup screen, expand the dimensions rows in the worksheet to view the measures rows you added, along with the data that is pulled in from the selected streams. Colored circle icons help you to identify and differentiate the types of measures, for example, the input measure has a yellow circle icon. You can hover over these icons to view the measure type name. The format of the measures values, such as percent or decimal place, comes from the underlying database. Where the formatting is not specified in the database, values are rounded to the nearest whole number.
View driver-based budget data in the underlying database
When the budget data entry is complete and you publish the budget workbook as a stream in the underlying database, you will see the published data for the measures.
You need to wait for the database to be rebuilt before you see the measures.
