Insert Formula columns

With Lumel EPM, it is possible to insert a new calculated row, column, or measure at the visual level in your Power BI table/matrix style reports, without writing DAX. The rows, measures, and columns so created can also be formatted, rearranged, and utilized for downstream calculations.

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 for two years, 2023 and 2024, by quarter. We will now try to insert a measure that calculates the percentage variance using the formula (2024 Actuals - 2023 Actuals) / 2023 Actuals.

To Insert a Formula Measure, Go to 'Home Tab' > 'Formula'.

The Formula measures can be inserted and be managed 'Home' > 'Manage Measures'.

In the 'Inserted Columns' pop-up, select 'Measures' tab > 'Insert New' > 'Formula'.

1. Insert As - Visual measure

b) In the Formula Measure pop-up that opens, enter the 'Title' of the inserted measure.

In the 'Insert as' field, you will see two options: Visual Measure (the default) and Visual Column. Let us go ahead with the default option - the visual measure.

The visual measure behaves like a measure, except that it is inserted directly in your visual (bypassing the data model).

c) Once you place the cursor inside the formula editor, you will see a context assistant pop-up. You can access other references or functions through this context assistant.

d) Let us go ahead and start typing in values. As you key in a formula, the References list automatically refreshes to show a narrower set of options.

You can directly select a column from the table to insert into the formula editor. Note that measures cannot be referred to in this way. To insert a visual measure or a visual column in your formula, you need to use the 'References' tab in the context assistant.

e) We will go ahead and complete the formula as shown below. The rectangular parentheses in the formula indicate that these are not normal text but references to other measures.

Click Create.

f) To convert the values of the Variance Measure to a percentage format, select the variance measure, click on the % icon from the 'Home' tab.

As you do this, you will also notice that the formula bar at the top of the matrix shows how the measure is calculated.

We have successfully inserted a Formula measure at the visual level in out Lumel EPM.

We have used the default 'Row aggregation type' which is 'Formula'. This property is covered in detail in the Number column section.

2. Insert as - Visual column

To insert a visual column, select ,Visual Column, option in Insert as within the Formula Measure pop-up.

In this example let us calculate the changes in the fourth quarter of 2023 and 2024. Select the 'Visual column' option.

Note that a visual column always appears at the last, outside any category or category hierarchies seen in columns.

Inserting Measures when there is no Column Level Heirarchy

When there is no column hierarchy, then there is no option to insert as a visual measure or column. In the example below, a formula measure called Increase is inserted which is calculated as 2024 Actuals/2023 Actuals.

Notice that there are Div/0! errors for the Australia row. There are two ways to deal with calculation errors.

Error Handling:

i) Appearance settings -> Suppress calculation errors

In the 'Settings' tab, click on 'Appearance'. In the Appearance panel, go to the 'Numbers' tab.

Turn on the 'Suppress calculation errors' toggle. You can see a new field called 'Custom error text'.

You can define a custom text, such as N.A., 0, or leave it blank. In the image below, we have entered 0. You can see the changes in the rows.

ii) Using IFNA function

Another way to handle calculation errors is by using the IFNA function. When an expression results in an error, you can replace it with a value as shown below.

iii) Using the IF function

Another indirect approach would be to use IF/nested IF statements to define the value when the expression results in an error.

3. Row and column aggregation for formula measures

Lumel EPM automatically sets the row aggregation and the column aggregation to sum for formula measures. You can override the default aggregation as required.

Note: When you select weighted average as the row aggregation type, the column aggregation will be set to weighted average and cannot be overridden.

You can view and edit the aggregation for formula measures from the Manage aggregation interface. Learn more about aggregation features in Lumel EPM.

Resources

Insert Formulas, Columns and Aggregation

Visual calculations using Inforiver

Calculate remaining budget in Power BI (without using DAX)

Insert calculated measure or column in Power BI

PreviousPerson NextManage inserted measures & columns

Last updated 7 days ago