Insert Formula rows

Lumel EPM provides an option to insert calculated rows using an intuitive formula editor. The Excel-like formula engine supports 50+ functions (logical, boolean, math functions, and more). The formula editor provides syntax, examples, and features such as autocomplete, multi-line support, and more to help users create, and troubleshoot formulas.

Refer to formula syntax for a detailed list of functions, operators, and identifiers that can be used for calculations.

Let us take this example, where we have sales data by country and category. Let's insert a row.

Select the Cell phones row above which we need to insert the row. In the Home tab, click on the Insert Row dropdown. Select the Formula option.

A blank row gets inserted and a side panel opens up as shown.

You can also create parent rows as calculated rows.

1. Formula editor

1.1. Rename the title and click on the formula editor. You can see a list of available functions in the Functions tab.

1.2. As you start typing, Lumel EPM's IntelliSense narrows down the list of functions to match the entered text. Click on MIN.

1.3. Lumel EPM displays the syntax for the function when you press ctrl + space. Click on the arrow highlighted.

1.4. Lumel EPM displays a description of the function and a detailed explanation of the arguments with examples.

1.5. To insert a reference to a particular row, click on the row when the cursor is placed in the formula editor.

1.6. The other way is to use the References tab. To access Computers, you need to type Australia.Computers. As you start typing, the references get narrowed down. Select 'Australia'.

Select 'Computers' from the dropdown.

By clicking on the expand button next to Formula, we can open the maximized formula editor view. Any errors in the formula can be easily located with clear and concise error messages.

In the maximized formula editor view, you will also notice line numbers, allowing you to quickly locate and fix errors.

1.7. In the maximized formula view, check the formula and click save.

The calculated row gets created as shown.

To see the underlying logic for calculated data, click on the cell to preview the formulae applied in the formula bar.

While you can use the SUM and AVERAGE functions to calculate the sum and average of rows respectively, you can also use the Aggregation option from the 'Insert Row' menu.

Select a parent-level row and choose the Aggregation option from the Insert Row menu.

Select the aggregation type from the dropdown menu.

2. Include in total

a) Check the 'Include in total' checkbox as we need the 'Play Station' value to be included in the Australia total and the grand total. Click update.

b) You can see that there is a formula bar on the top which shows the formula defined.

c) Let's uncheck the 'Include in total' checkbox and click 'Update'.

d) You can see that the grand total and Austarlia's total have been updated, excluding Play station.

3. Evaluate Formula for Custom.

When inserting calculated rows, in some cases you might want the total to be the sum of the child columns whereas in other cases, it needs to follow the defined formula. This behavior can be configured using the 'Custom' option under 'Evaluate column For'.

Let's consider an example where we are inserting the relative variance between two columns.

In the image below, with the 'Custom' option enabled, you can see that the variance for the grand total is calculated based on the values of 2021 Actuals and 2022 Actuals.

When 'Custom' is disabled, the grand total variance is calculated as the sum of 2021 Actuals and 2022 Actuals which is not the expected behavior in this case.

4. Custom scaling

In certain cases, the values in calculated rows may not conform to the number scaling set at the report level. You can apply a custom scaling factor based on the nature of the calculation applied.

5. Bind for Cross filter/RLS

You can enable the Bind for Cross filter/RLS option – this ensures that cross-filter selections and RLS settings apply to calculated/manual input rows that reference other rows.

For example, if this option is not enabled, a manager handling Canada accounts can view a manually inserted row that references Australia data or an inserted region that is manually created at the visual level.

You can bind inserted rows by selecting a reference row or a dimension category.