Insert calculated columns

In Premium Table, you can insert a new calculated row, column/measure at the visual level in your Power BI table without writing DAX. The rows and columns that are created can also be formatted, rearranged, and used for subsequent 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 the previous year and the current year. We will now try to insert a column that calculates the percentage variance using the formula ((Actual sales - PY Sales) / PY Sales) * 100.

Sales - current year and previous year
  1. To insert a formula column, select Insert Formula from the Insert tab. A side panel opens on the right.

New formula column
  1. 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
  1. Let us go ahead and start typing in values. As you key in a formula, the References and the Functions list automatically refresh to show a narrower set of options.

results

You can directly select a column from the table to insert its reference in the formula editor.

  1. 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 measures.

Formula entered
  1. Give the calculated column an appropriate name, say 'Variance %', and click on Create.

Creating a calculated column
  1. You would have now inserted a calculated column in your report.

Calculated column inserted

Alternatively, you can calculate absolute variance as shown below, then convert and format the results into percentages.

Absolute variance
  • To convert the values to a percentage format, select the column and click on the % icon from the 'Home' tab.

Results in percentages

We have successfully inserted a calculated column at the visual level in our Power BI table report.

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

Handling 'divide by zero' error messages

There are two ways to deal with the Div/0! 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.

Display settings

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." or "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.

Using IF function

Last updated