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 Cost of Goods Sold (COGS) and Gross Margin by brand category and variant. Let's insert a row.

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

Inserting a calculated row

A blank row is inserted, and the Calculated Row side panel opens up as shown.

Create calculated row

While creating Formula rows Create button is enabled only after the Formula is entered.

You can also create parent rows as calculated rows.

Formulated Parent Row - Amber Ale is created

1. Formula editor

The Formula Editor within the Calculator row pop-up in Lumel EPM offers a range of features designed to enhance usability and streamline the user experience. We will see them below.

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

Formula Editor

1.2. As you start typing the formula, Lumel EPM's IntelliSense narrows down the list of functions to match the entered text. In the image below, we have typed in m, and all the functions beginning with the letter m are listed. Click on the MIN function.

Suggestion Toggle should be enabled to view Function and Reference Suggestions

Select function

1.3. Once the function is selected, Lumel EPM displays the syntax, description and a detailed explanation of the arguments with examples for the function when you press Ctrl + space.

Formula, syntax, description, and examples of Min are displayed

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

Referencing rows by selecting them

1.5. The other way is to use the References tab. To access Dark Roast, you need to type Artisan Ale.Beer.Dark Roast. As you start typing, the references get narrowed down based on the brand, category, and variant.

References of rows within the Formula

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.

Maximized Formula Editor

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

The calculated row gets created as shown.

Formula Row - Bubble Burst is inserted, whose formula is displayed in the formula bar

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.

Aggregation- Inserting aggregated rows

Select the aggregation type from the dropdown menu.

Aggregation Types

To know more about these aggregation types in detail, click here.

2. Include in total

a) Check the 'Include in total' checkbox, as we need the value of 'Bubble burst' to be included in the total of Beer, which is its parent.

Unchecking the Include in Total will exclude values of the inserted row from the total of Beer.

Including the calculated row in total

3. Evaluate Formula for Custom

When we have a Formula column and Formula Row inserted, at the intersection cell, we can choose to include or exclude the Formula of the Column by configuring the Custom option available under the 'Evaluated Formula For' option.

Let's consider an example, we have a Total Column that sums up the COGS and Margin values, and Bubble burst is a Formulated row.

In the Bubble Burst row, we have configured it such that the Total column is included.

Total Column is included

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.

Scaling Factor

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 Artisan Ale accounts can view a manually inserted row that references AquaSplash 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.

5.1 Binding by selecting a row

To bind a particular reference row, choose Row from the Selection Type dropdown.

The Bubble Burst row references the Dark Roast Variant under the Artisan Ale brand in the example below.

Bind by row selection

Post Binding the Bubble Burst row is displayed for users with RLS access to Dark Roast variant data only.

Bubble Burst Row is row bound to Dark Roast

5.2 Binding by Dimension category

To bind the inserted row to a particular dimension category, choose Dimension member from the SelectionType dropdown.

In the example below, the Lager brand references the Summit Lager variant. The Lager brand is Dimension Bound to the Summit Lager brand.

Lager brand is Dimension bound to the Summit Lager brand

Notice how the Lager brand is displayed for users with access to the Summit Lager brand.

Lager is Dimension-bound to Summit Lager

Resources

Insert Formulas, Columns and Aggregation

Visual calculations using Inforiver

Implement SUM measure totals in Power BI (without using DAX)

Excel-like MoM, QoQ, YoY % Calculations in Rows in Power BI