# Insert calculated columns With Inforiver, you can insert a new calculated row, column, or measure at the visual level in your Power BI table or 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. {% hint style="info" %} Refer to [formula syntax](https://docs.inforiver.com/formula-syntax) for a detailed list of functions, operators, and identifiers that can be used for calculations. {% endhint %} ### 1. Visual measure Let us take this example, where we have sales data for two years, 2024 and 2023, by quarter. We will now try to insert a measure that calculates the percentage variance using the formula (2024 Actuals - 2023 Actuals) / 2023 Actuals.

**STEP 1:** To insert a measure, select **Insert Formula** from the **Insert** ribbon. A side panel opens on the right.

**STEP 2:** 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 - **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.

**STEP 3:** Once you place the cursor inside the formula editor, you will see a context assistant pop-up. You can access dimension/measure references or functions through this context assistant.

**STEP 4:** 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. {% hint style="info" %} You can directly select a column from the table to insert in the formula editor. Note that measures can not be referred to 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. {% endhint %}

Using intellisense to narrow down the reference list

**STEP 5:** 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.

**STEP 6:** Give the calculated measure a relevant name, say 'Variance %', and click **Create**.

**STEP 7:** 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.

**STEP 8:** 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.

We have successfully inserted a calculated measure at the visual level in our Power BI matrix report. ### 2. Visual column A calculated column works differently from a calculated measure. Let's consider two examples. #### Example 1: **STEP 1:** Let us calculate the quarter-on-quarter change from Q3 to Q4. Select the **Visual Column** option. Note that a visual column always appears at the end, outside any category or category hierarchies seen in columns.

**STEP 2:** You will also notice that the **References** section is a bit different. While creating a [new measure](#1.-visual-measure), the **References** section only listed the available measures (2024 Actuals and 2023 Actuals). This time, it displays every instance of the individual measures (e.g., Q2 - 2024 Actuals, Q3 - 2023 Actuals, etc.). Using this, you can traverse right up to the leaf node of any column hierarchy.

References when creating a visual column

**STEP 3:** Enter a title and the formula and click **Create**.

**STEP 4:** You will see the values updated in the report.

#### Example 2: Let's consider an example where we use built-in functions. In the example below, we want to insert a column that displays the multiplier - 2022 Actuals/2022 Plan. **STEP 1:** Click **Insert Formula**. Notice that there is no option to insert as a visual measure or column. This is because the report doesn't have a column hierarchy.

Visual columns are automatically created when the report doesn't have a column hierarchy

**STEP 2:** Enter the title and the formula. Click **Create**.

**STEP 3:** 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. Turn on the **Suppress Calculation Errors** toggle. A new field called **Custom Error Text** is enabled.

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

Replacing error messages with custom 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.

**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** Inforiver automatically sets the row aggregation to formula and the column aggregation to sum for formula measures. You can override the default aggregation a 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 Inforiver.](https://docs.inforiver.com/working-with-inforiver/manage-aggregations)

Row and column aggregation for formula fields

#### Resources [Insert Formulas, Columns and Aggregation](https://www.youtube.com/watch?v=hjPAbuYJUSc) [Visual calculations using Inforiver](https://inforiver.com/webinars/visual-level-formula-calculations-powerbi/) [Calculate remaining budget in Power BI (without using DAX)](https://inforiver.com/blog/general/calculating-remaining-budget-powerbi/) [Insert calculated measure or column in Power BI](https://inforiver.com/blog/feature-highlights/insert-visual-measure-column-calculations-in-microsoft-power-bi/)