Comment on page
Insert calculated columns
With Inforiver, 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, 2021 and 2020, by quarter. We will now try to insert a measure that calculates the percentage variance using the formula (2021 Actuals - 2020 Actuals) / 2020 Actuals.
Sales for 2021 and 2020 by quarter
a) To insert a measure, select 'Insert Formula' from the 'Insert' tab. A side panel opens on the right.
Visual measure
b) You will see two options in the 'Insert as' field: 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). In the image below, note that this new measure is inserted under each quarter.
Visual measure
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.
List of references
List of functions
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 in the formula editor. Note that measures can not be referred by using this way. To insert a measure in your formula, you need to use the 'References' tab in the context assistant.
Reference list
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.
Formula entered
f) Give the calculated measure a proper name, say 'Variance %', and click 'Create'.
Creating a measure
g) You would have now inserted a calculated measure in your report. However, you will notice that the values are in absolute numbers and not in percentage terms.
Measure inserted
h) To convert the values to a percentage format, 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.
Formula bar showing the calculation
We have successfully inserted a calculated measure at the visual level in our Power BI matrix report.
We have used the default 'Row aggregation type' which is 'Formula'. This property is covered in detail in the Number column section.
A calculated column works differently from a calculated measure. Let's consider two examples.
a) Let us calculate quarter-on-quarter change from Q3 to Q4. Select the 'Visual column' option. Note that a visual column always appears at the last, outside any category or category hierarchies seen in columns.
Visual column
b) You will also notice that the References section is a bit different. While creating a new measure, the References section was just listing other available measures (2021 Actuals and 2020 Actuals). This time, it shows every instance of the individual measures (e.g. Q2 - 2021 Actuals, Q3 - 2021 Actuals etc.). Using this, you can traverse right up to the leaf node of any column hierarchy.
Reference list
c) Enter a title and the formula and click 'Create'.
Creating a visual column
d) You will see the values updated in the report.
Quarter on quarter column
Let's consider an example where we use some functions. In the below example, we want to insert a column which displays the multiplier - 2022 Actuals/2022 Plan.
a) Click on 'Insert formula'. Notice that there is no option to insert as a visual measure or column. This is because there is no column hierarchy.
Inserting a new column
b) Enter the title and the formula. Click 'Create'.
Defining the calculation
c) Notice that there are Div/0! errors for two of the rows. There are two ways to deal with calculation errors.
i) Display settings -> Suppress calculation errors
In the 'Home' tab, click on 'Display' settings. In the side panel, go to the 'Numbers' tab.
Suppress calculation errors
Turn on the 'Suppress calculation errors' toggle. You can see a new field called 'Custom error text'.
Suppress calculation errors
You can define a custom text such as N.A., 0 or leave it blank. In the below image, we have entered 0. You can see the changes in the rows.
Define custom error text
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.
Using IFNA function
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.
Last modified 4mo ago