Insert calculated rows

Inforiver provides an option to insert calculated rows using an intuitive formula editor. 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 by category and subcategory. Let's insert a row for Specialty Beverages which is calculated as the minimum of the other subcategories in Beverages across quarters.

a) Select the Tea & Coffee row above which we need to insert the row. In the 'Insert' tab, click on the 'Insert row' dropdown. Click on 'Calculated row'.

b) A blank row gets inserted and a side panel opens up as shown.

1. Formula editor

a) Rename the title and click on the formula editor. You can see a list of available functions in the 'Functions' tab.

b) As you start typing, the list of functions narrows down to match the entered text. Click on 'Min'.

c) You can see the syntax for the function. Click on the arrow highlighted.

d) You can see an explanation of the arguments and examples.

e) To insert a particular row, you can simply click on it when the cursor is placed in the formula editor.

f) The other way is to use the 'References' tab. To access Soda, you need to type Beverages.Soda. As you start typing, the references get narrowed down. Select 'Beverages'.

g) Type a '.' after Beverages and select 'Soda' from the dropdown.

h) Finish typing the formula as shown below.

While you can use the SUM and AVERAGE functions to calculate the sum and average of rows respectively, you can also use the Insert Sum of rows and Insert Avg of rows options from the 'Insert Row' menu.

Select the desired rows first holding down the Ctrl key and then select Insert Sum of rows.

A new row containing the sum of the selected rows is created as shown below. You can use the side panel to make any additional changes. Observe how the formula editor has been automatically populated with the selected rows.

Similarly, for calculating and inserting the average of rows, use Insert Avg of rows.

Any errors in the formula can be easily located with clear and concise error messages. In the maximized formula editor view, you will also notice line numbers, allowing you to quickly locate and fix errors.

2. Include in total

a) Check the 'Include in total' checkbox as we need the Specialty Beverages value to be included in the Beverages total and the grand total. Click 'Create'.

b) You can see that the row is renamed and the values are populated. There is also a formula bar on the top which shows the formula defined.

Note that the grand total is now updated to 255m.

c) Let's uncheck the 'Include in total' checkbox and click 'Update'.

d) You can see that the grand total has now reverted back to the original value of 239m.

3. Include in charts

a) Check the 'Include in charts' checkbox and click 'Update'.

b) Let's now add a bar chart to 2022 Actuals. Click on the column and select the highlighted bar chart from the 'Home' tab.

c) Notice that the Specialty Beverages row is also visualized using the bar chart.

d) On unchecking the 'Include in charts' checkbox and clicking 'Update', you can see that the calculated row is no longer shown as charts.

4. Evaluate column before rows

When inserting calculated rows, in some cases you might want the total to be the sum of the child columns whereas in other cases, it needs to follow the defined formula. This behavior can be configured using the 'Evaluate column before rows' option.

Let's consider an example where we are inserting the relative variance between two rows. In the below image, with 'Evaluate columns before rows' enabled, you can see that the variance for the grand total is calculated based on the grand total values of Pacific and Central.

When 'Evaluate columns before rows' is disabled, the grand total variance is calculated as the sum of Beverages and Water which is not the expected behavior in this case.

5. Custom scaling

In certain cases, the values in calculated rows may not conform to the number scaling set at the report level. You can apply a custom scaling factor based on the nature of the calculation applied.

6. Bind for Cross filter/RLS

You can enable the Bind for Cross filter/RLS option – this ensures that cross-filter selections and RLS settings apply to calculated/manual input rows that reference other rows. For example, if this option is not enabled, a manager handling Canada accounts can view a manually inserted row that references US data or an inserted region that is manually created at the visual level.

You can bind inserted rows by selecting a reference row or a dimension category.

• Binding by selecting a row

To select a particular reference row, choose Row from the SelectionType dropdown. The Alaska>Inforiver hierarchy references the Canada row in the example below.

The Alaska hierarchy is displayed for users with RLS access to Canada data.

• Dimension category

To bind the inserted row to a particular dimension category, choose Dimension member from the SelectionType dropdown. In the example below, the Paseo adjustment row references the Paseo product.

Notice how the adjustment row is displayed for users with access to Paseo products.

Resources

Insert Formulas, Columns and Aggregation

Visual calculations using Inforiver

Implement SUM measure totals in Power BI (without using DAX)

Excel-like MoM, QoQ, YoY % Calculations in Rows in Power BI