Additional measures for driver-based budgeting

Owned by Denise McGettigan
Last updated: Apr 01, 2024
12 min read

By default, in each worksheet row, there is only one measure (such as Sales) but you can include additional measures. For example, add the Quantity and Price measures to calculate the sales value.

The option to include additional measures is available when you create an operational budget or add a Database tab or Reference tab, where you define the additional measures by adding rows in the setup. The following row types are available:

Row type

Description

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. Data can be entered into these rows during the budgeting process, then published to a measure in the underlying database.

 

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. The results can be published to a measure in the underlying database.

 

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. Data can be entered into these rows during the budgeting process, then published to a measure in the underlying database.

Before you start - key concepts and example

Before you start adding additional measures, take a minute to learn about some of the key concepts that might be new to you.

Measures and calculated measures

The Measure dropdown contains a list of the measures you can add as rows in the worksheet. The available measures depend on the selected stream in the underlying database. In Input and Calculation rows, you can only use a measure once. When you select a measure in one of those rows, it becomes unavailable for selection in subsequent rows. It is possible to select the same measure in a Reference row because the data is not published back to the stream.

Some measures have a yellow calculator icon next to them to signify they are a calculated measure in the underlying database. Calculated measures are not published to the underlying database. For example, in the image below, you might input the profit when budgeting, however, as this is a calculated measure (Local Value - Cost), it will NOT be published back to the underlying database.

Hover over the icon to view the underlying formula.

Aggregation

The Aggregation 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.

Example of the process

The following example shows how to add Input, Calculation and Reference rows, and resolve the warning message that displays when elements of a calculated measure formula are missing. It also shows how to enter some budget values to derive other values automatically.

The context of this example is a Sales budget that is organized by Sales Rep > Product Class.

In the Additional measures for driver-based budgeting screen in the budget setup:

  1. Calculate the Sales - Local Value (the quantity that you are going to sell multiplied by the price).

    1. Add an Input row for Quantity and select the Quantity measure.

    2. Add an Input row for Price and select the price measure, which in this database is a calculated measure, Local Value / Item.

    3. Convert the primary Input row to a Calculation row and enter the formula: = Quantity * Price.

  2. Calculate the profit:

    1. Add an Input row for Gross Profit % and select the local margin measure, which in this database is a calculated measure, Local Margin %.

      1. A warning message displays in the FORMULA aggregation box to let you know that the database does not have the information to calculate this measure - one or more of the formula elements are missing. In this case, the Local Cost value is missing.

      2. You can ignore this warning message if you are using this row for display purposes. However, if you want to use this row to enter data, you cannot publish that data to the calculated measure. You need to publish both the data to the Local Value and Local Cost measures. You have the Local Value (primary row) but you do not have the Local Cost, so you need to add other rows to obtain that measure. As the cost is determined by the local value minus the profit, you need to add a row for the profit and another for the cost.

    2. Add a Calculation row for Profit $, enter the formula: = Sales-Local Value * Gross Profit % / 100. There is no need to select a measure in this case.

    3. Add a Calculation row for Local Cost, enter the formula: = Sales-Local Value - Profit $ and select the Local Cost measure. As soon as you complete this step, the warning message disappears, as the Gross Profit % formula now has all the required elements.

  3. Include reference data:

    1. Add a Reference row for Last Year’s Profit and select the Local Profit calculated measure, then move the row up to sit below the Profit $ row.

    2. Add a Reference row for Last Year’s Cost and select the Local Cost measure.

  4. Review the additional rows that will be added to the budget and note that when the budget is published to a stream in the database:

    • The following data will be published: Local Value, Quantity and Local Cost.

    • The following data will NOT be published, as they are calculated measures: Price (determined by Local Value / Quantity), Local Margin % (determined by ((Local Value - Local Cost) / Local Value)*100) and Local Profit (determined by Local Value - Local Cost).

  5. Click Save and Finish.

  6. Review the additional rows that were added to the budget. Note that the data in the Reference rows should match the data in the Calculation rows, except for some rounding errors.

  7. Enter the budget values into the three yellow Input rows. For example, suppose you enter a quantity of 100, price of 300 and the margin of 20% for February, the local value (30,000) and the local cost (24,000) are automatically derived.

  8. Publish the budget to the stream, then view the published data (measures) in the database.

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

  1. Click the blue Add button on the right side of the screen and select Input or Reference as required.

  2. Enter a name for the row.

  3. Change the default stream (data source), if required.

  4. Select the measure.

  5. Change the default aggregation method, if required.

  6. Change the offset (baseline period), if required.

  7. Customize the display format of the measures, if required: Click the menu button (three dots) at the end of the row and switch to Custom format, then define your format.

  8. Lock the row for editing by certain users, if required (see section below).

See the Use formulas page to learn about the functions you can use in the calculations.

  1. Add the other rows that you want to include in your calculation.

  2. Click the blue Add button on the right side of the screen and select Calculation.

  3. Enter a name for the calculation.

  4. Enter the calculation formula:

    1. 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.

    2. 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.

       

  5. Select the measure in the underlying database to publish the calculation result to, if required.

  6. Change the default aggregation (FORMULA) method, if required.

  7. Customize the display format of the measures, if required: Click the menu button (three dots) at the end of the row and switch to Custom format, then define your format.

  1. Click the blue Add button on the right side of the screen and select Working.

  2. Enter a name for the row.

  3. Select the measure in the underlying database to publish the data to, if required.

  4. Select the aggregation method.

  5. Customize the display format of the measures, if required: Click the menu button (three dots) at the end of the row and switch to Custom format, then define your format.

  6. Lock the row for editing by certain users, if required (see section below).

Mark a row as driven (Database tabs only)

If you are adding rows in the setup of a Database 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 can manage who can edit individual Input and Working rows. 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 pricing manager (administrator), then lock that row, so that budget contributors cannot edit the price data.

Three editing options are available, in order of full to restricted access:

  • Everyone (default) - all budget contributors can edit the values in the row.

  • Budget administrators - only the budget administrator(s) and owner can edit the values in the row (and change this setting).

  • Budget owner - only the budget owner can edit the values in the row (and change this setting).

Click the menu button (three dots) at the end of the row, then select the required role.

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.