Customize the format of measures

Owned by Denise McGettigan
Last updated: Feb 12, 2024
7 min read

Sometimes the precision of a value is important. Budgeting items like gross profit margins, product prices, exchange rates, and so on often requires at least two decimal places, sometimes more. For example, you might want to budget revenue and expense line items to the cents (or pence) level or include a suffix to identify the currency.

Custom measure formatting allows you to override the default formatting of the values you see in a worksheet. That default formatting comes from the measures in the underlying database. You can define the number of decimal places each measure has, along with any prefix or suffix information.

If you have access to Designer, you will be able to check the format of the measures in a database. In the following example, additional measures were added to a Sales budget, taking the source format from the corresponding measures in the underlying datbase. Both the Quantity and Margin measures have two decimal places and the Margin also has a percentage sign suffix.

image-20240212-015313.png

When you view these rows in the budget, you can see they have different formatting, and neither formatting is the same as the parent row.

image-20240212-015844.png

It is important to remember that when you define a custom measure, your changes are visual only; you only change how the values in the worksheet (grid) display for you and other users. You don’t make any changes to the measures in the underlying database design. In the case of a value that has been rounded to zero decimal places, you can still see the full amount in the fx bar above the grid.

The following examples demonstrate how you can apply a custom format to measures in different places throughout Budgets and Forecasts.

Format measures in a financial budget

In a financial budget, the rows are derived from the underlying financial statement. If a custom measure format is applied to an item in the statement setup, that format is carried through to the budget and can’t be overridden by the custom format you apply in the budget setup. For example:

  • In image 1, you can see the Profit and Loss statement setup window, where the Gross Profit row has a default format but the Gross Profit % row has a custom format.

  • In image 2, you can see the budget setup is based on that statement and a custom format of 0 decimal places is applied.

  • In image 3, you can see the budget in which all the measures have the custom format (0 decimals) as per the budget setup except for the Gross Profit % row, which retains its custom format from the financial statement.

This video introduces the concept of custom measure formatting and shows how to apply a custom format of a dollar sign prefix and two decimal places to the measures in a financial budget.

Format measures in an operational budget with additional measures

This video shows how to apply a different custom format to the additional measures that were added to a Sales budget for driver-based budgeting:

  • Quantity: No decimal places.

  • Price: Two decimal places.

  • Margin: Two decimal places with a percentage sign suffix.

  • Cost: Two decimal places.

  • Working days: One decimal place to show where there are half working days.

  • Sales per day (working row with formula): Zero decimal places (default) changed to two decimal places. This type of row doesn’t have any source formatting.

Format measures in comparison rows

The first (default) comparison row in the Main tab, Database tab, or Reference tab of a workbook gets its formatting from the parent row of the respective tab, which in turn comes from the tab setup. Any subsequent comparison rows you add will get their formatting from the selected measure in the underlying database. For example, in image 1, you can see two comparison rows showing the same measure but with different formatting. The first comparison row got its formatting from the parent row by default (image 2). The second row got its formatting from the measure, which has custom formatting applied (a dollar sign prefix) in the database design (image 3).

This video shows how to apply a custom format to the comparison rows in a Reference tab. The process is the same for adding comparison rows in the Main and Database tabs.

Format measures in working and sum rows

By default, the measures in working and sum rows take on the same formatting as the measures in the parent row, which comes from the budget or workbook tab setup. There’s no source formatting option for either row type, as their values don't come from an underlying database.

This video shows how custom formatting works differently in the working and sum rows:

  • Working row: You can apply a custom format via the menu button to the left of its name. In this example, you add two decimal places and a dollar sign prefix. When applied, the format of the working row values is different from the format of the parent line.

  • Sum row: You don't have the option to apply a custom format because this row sums up to its parent row. Even though you can enter a value with decimal places (visible in the fx bar), those decimals are ignored in the worksheet for display purposes.

Format measures in Database and Reference tabs with additional measures

This video shows how to apply a custom format to the various measures you can add to a workbook via a Database tab and Reference tab. The process is the same, as both of these tabs have a similar setup, including the option to have additional measure rows for driver-based budgeting. However, there are differences related to the types of rows you can add:

  • Input and Reference rows: As you must select a measure from the underlying database, the source formatting comes from that measure. You can override this with your custom formatting.

  • Calculation, Working, Days, and Lookup rows: If you don’t publish to a measure, you can only define a custom format, as there is no source format to use. If you do publish to a measure, you have the option to the source format rather than defining your own.

Format measures in a Lookup tab

The measures you use in a Lookup tab don't have a source format option because there's no source for them. They are like working lines in that their initial value doesn't come from an underlying database. By default, they are rounded to zero decimal places and don't have a prefix or suffix.

This video shows what the default measures look like and how to apply a different custom format to the following lookup items:

  • Exchange rate: Six decimal places.

  • Sales growth rate: Two decimal places and a percentage sign suffix.

Format measures in a Manual Entry tab - coming soon

The option to format the measures in a Manual Entry tab is not available yet but coming soon. The process will be similar to how it’s done in working rows, where you can format each row individually.

Format measures in a Balance Sheet tab

In the Balance Sheet tab, you can switch between the source formatting (which comes from the selected measure) and your custom formatting.

If you add mini drivers, there’s no source format, as there’s no measure associated with those rows but you can define a custom format.

If you include a Cash Flow tab, the formatting for the measures in that tab comes from Main tab and you can’t change that.