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.

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.

1. To insert a formula column, select Insert Formula from the Insert tab. A side panel opens on the right.

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.

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.

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.

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

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

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

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

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.

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." or "0" or leave it blank. In the below image, 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.