Inforiver
FormulaPricingProductAll Products
Reporting & Writeback Matrix Docs
Reporting & Writeback Matrix Docs
  • Introduction to Inforiver
    • Why Inforiver
    • Get Started
      • Installing Inforiver for yourself
      • Installing from AppSource
      • Installing Inforiver for your organization (Org visual)
      • Inforiver Writeback Matrix pre-requisites
        • Whitelist Inforiver IPs
        • Admin consent for Inforiver's Entra ID
          • Basic consent
          • Full admin consent
      • Inforiver Customer Portal
        • Billing Admin
          • Update Profile
          • Update Subscriptions
        • License Admin
      • Licensing
        • License renewal
      • Registering Inforiver on Azure AD (Entra ID)
  • Working with Inforiver
    • 1. Build your first Inforiver report
    • 2. Displaying information
      • Report interface
      • Layout options
      • Templates
      • Basic formatting
        • Number formatting
        • Cell, header & value formatting
        • Totals & subtotals
        • Insert blank rows
      • Basic inline charts
    • 3. Basic interactions
      • Explore & filter data
        • Search & Filter
        • Explorer
        • Top N + others
      • Sort & reorder data
      • Show/hide values
      • Manage column widths
      • Manage hierarchies
      • Create visual hierarchies
      • Pin rows & columns
      • Blend measures
      • Dynamic measure headers
      • Context menus
        • Column gripper
        • Row gripper
      • Actions
        • Rails
        • Toolbar options
        • Keyboard shortcuts
        • Reset
    • 4. Adding business logic and formulae
      • Insert calculated rows
        • Template rows
      • Insert calculated columns
      • Insert manual input rows
      • Insert manual input columns
        • Number
        • Dropdown
          • Dropdown options from semantic models
          • Lookup visuals for dropdowns
        • Text, checkbox & date
        • Writeback Matrix features
      • Invert sign
      • Editing cells
      • Quick formula
      • Visual column inside visual measures
      • Manage inserted rows
      • Manage inserted measures & columns
      • Pivot data
      • Insert image URLs
    • 6. Working with charts
      • Sparkline charts
      • Stacked charts
      • Bullet charts and clustered bar charts
      • Waterfall charts
      • Bar and pin charts
      • Change/deviation charts
    • 5. Conditional formatting
      • One-click options
      • Create rule
        • Rules (If conditions)
        • Color scale & data bars
        • Classification
        • Ranking
      • Manage rules
    • 7. Planning, budgeting & forecasting
      • What-if analysis & simulations
      • Goal Seek
      • Scenarios (Writeback Matrix only)
      • Budgeting & allocations
      • Forecasting
        • Date rollover and extending forecasts
        • Editing forecasts and reforecasting
        • Autoclose forecasts
        • Distribute deficit
        • Row level forecast
        • User Access Control
        • Common Scenarios
          • Forecast using a Blank Template
          • Forecast using Existing Data
          • Adding Forecasts for Periods unavailable in the model
          • Creating daily and weekly forecasts
      • Set version
    • 8. Paginated reporting
      • Pagination
      • Header & footer
        • Text
        • Images
        • Charts
        • KPI cards
      • Report layouts (Report+)
      • Display and themes
    • 9. Commenting and collaboration
      • Notes
      • Comments
        • Data-level comments
        • Assign & track tasks
        • Track changes
        • Report-level comments
        • Comment settings
    • 10. Exporting reports
      • Export to PDF & Excel
        • Excel export - operational boundaries
      • Ad-hoc emails
      • Snapshot (Writeback Matrix only)
        • Comparing snapshots
    • 11. Scheduling reports
      • Create new subscription
        • Select a report
        • Set subscription frequency
        • Select destination(s)
          • Email
          • OneDrive
          • Microsoft Teams
          • SharePoint
        • Review & save subscription
      • Manage your subscriptions
      • Administration
    • 12. Data writeback
      • Settings
        • General Settings
        • Data Settings
        • Destination Settings
        • Writeback security settings
        • Writeback time-out & batch size settings
        • Advanced Settings
      • Destinations
        • Azure SQL (Managed DB)
        • Azure SQL
        • Microsoft SQL Server
        • Synapse Dedicated SQL Pool
        • Azure Data Lake Storage
        • Databricks
        • Snowflake
          • Setting up Snowflake OAuth
        • Amazon Redshift
        • BigQuery
        • SingleStore
        • SAP HANA
        • Oracle
        • PostgreSQL
        • MySQL
        • OneDrive
        • SharePoint
        • Dataverse
        • Fabric Warehouse
        • Fabric lakehouse
        • Fabric SQL
        • URL as destination
      • Logs
      • Reading mode
    • 13. Context awareness
    • 14. Manage aggregation
      • Row aggregation
      • Column aggregation
    • 15. Managing Inforiver Writeback Matrix visuals
      • Inforiver deployments in Power BI pipeline setup
      • Duplicating Writeback Matrix Visuals - Best Practices
      • Recommended practices while working with Power BI desktop
    • 16. Approval workflows
    • 17. Collaborative adjustment workflows
    • 18. Variables
      • Variable types
        • Number
        • Select
        • Text
        • Date
        • Date range
        • Boolean
        • Button
      • Variable bookmarks
    • 19. Leveraging Power BI bookmarks
  • Admin Console
    • Manage Profile
    • User management
      • User Roles and Permissions
      • Create custom roles and policies
    • Settings
      • General
      • Generate builds - on prem
      • Forecast
      • Holidays
      • API token
      • Writeback
      • Managed DB
      • Assets
    • Onboarding actions
    • Writeback
  • Display settings
    • General settings
    • Hierarchy settings
    • Number settings
    • Miscellaneous settings
    • On-load scripting
  • Formula syntax
    • Conditional statements
      • IF
      • IFNA
      • SWITCH
      • FILTERIF
    • Logical functions
      • AND
      • IN
      • ISBLANK
      • ISEMPTY
      • ISNUMBER
      • NOT
      • OR
      • XOR
    • Math functions
      • ABS
      • AVERAGE
      • AVERAGEEXNEG
      • AVERAGEEXZERO
      • AVERAGEEXZERONEG
      • AVERAGEIF
      • CEILING
      • COUNT
      • COUNTIF
      • DIVIDE
      • EVEN
      • EXP
      • FLOOR
      • INDEXOF
      • LOG
      • ODD
      • MAX
      • MIN
      • NORMDIST
      • NORMSDIST
      • NORMINV
      • NORMSINV
      • PCT
      • POWER
      • RAND
      • RANDBETWEEN
      • ROUND
      • SORT
      • SQRT
      • SUM
      • PMT
    • Text formatting functions
      • CONCATENATE
      • HYPERLINK
      • LEFT
      • LOWER
      • MID
      • PROPER
      • REPLACE
      • REPT
      • RIGHT
      • TEXT
      • TRIM
      • UPPER
      • VALUE
    • Date functions
      • DATEADD
      • DATEDIFF
      • DAY
      • DATE
      • DATE.SET
      • DATE.FORMAT
      • SOWEEK
      • SOMONTH
      • SOQTR
      • SOYEAR
      • EOWEEK
      • EOMONTH
      • EOQTR
      • EOYEAR
      • NETWORKDAYS
      • ADDDAYS
      • ADDWEEKS
      • ADDMONTHS
      • ADDQTRS
      • ADDYEARS
      • FROMEXCELDATE
      • MONTH
      • NOW
      • PERIOD_RANGE
      • TODAY
      • TOEXCELDATE
      • YEAR
    • Time intelligence functions
      • AGGREGATE
      • FILTER
      • COLUMN_PERIOD
      • LASTNDAY
      • LASTNMONTH
      • LASTNQTR
      • LASTNYEAR
      • MONTHPERIOD
      • MOVINGAVERAGE
      • MOVINGSUM
      • MTD
      • NEXTNDAY
      • NEXTNMONTH
      • NEXTNQTR
      • NEXTNYEAR
      • QTD
      • QTRPERIOD
      • SHIFT
      • TOTALYTD
      • TOTALQTD
      • TOTALMTD
      • VALUEAT
      • YEARPERIOD
      • YTD
    • Other operators
    • Identifiers
      • CLOSEDPERIOD
      • COLUMN.DATE
      • COLUMN.PARENT
      • COLUMNS
      • CURRENT_PERIOD
      • DESCENDANTS
      • FORECAST.CLOSED_END
      • FORECAST.CLOSED_START
      • FORECAST.OPEN_END
      • FORECAST.OPEN_START
      • GROUP INDEX
      • HAS
      • HAS_ALL
      • HAS_SOME
      • LEAVES
      • LEVEL
      • LOGGEDIN_EMAIL
      • LOGGEDIN_NAME
      • MATCH
      • MAXDATE
      • MEMBERS
      • MINDATE
      • RELATIVE
      • RELATIVE_COLUMN
      • ROW.PARENT
      • ROW.LABEL
    • Scripting functions
      • DISPLAYTOAST
      • EXPORT
      • LAYOUT
      • SETCFRULE
      • SETFILTER
      • SETFILTERBYINDEX
      • SETVISIBILITY
      • SHOWBETWEENPERIOD
      • SHOWCLOSEDFORECAST
      • TOPN
      • DISPLAY SETTINGS
        • SETBLANKLABEL
        • SETSUPPRESSZEROS
        • SETRAGGEDHIERARCHY
        • SEPARATOR
        • SETCOMBINECOLUMNHEADER
        • SEMANTICFORMAT
        • SHOWNULLAS
        • SHOWZEROAS
        • SINGLECHILD
    • Miscellaneous functions
      • FETCH
      • LOOKUP
    • Measure/Column selection functions
      • GETCOLBETWEENPERIODS
      • GETMEASURE
  • Advanced topics
    • Audit log
    • Backup & restore report config
    • Enterprise themes
    • PowerPoint integration
    • Drill down and roll up in performance mode
  • Infobridge
    • 1. Create bridge
    • 2. Add source to bridge
    • 3. Bridge Interface
    • 4. Manage queries
      • Query transformations
        • Append
        • Join
        • Duplicate
    • 4. Manage sources
    • 5. Insert rows from bridge
    • 6. Insert measure from bridge
    • 7. Manage Integration of visuals
    • 8. Manage Bridges
    • 9. Data transformations
      • Pivot table
      • Group by
      • Add Column
      • Pivot column
      • Rename column
      • Find and Replace
      • Remove measure
      • Filter rows
      • Sort
      • Text functions
      • Unnest
      • Round
      • Extract Comments
      • Import and export
    • 10. Integrating forecasts
    • 11. Collaborative adjustments
  • Accessibility shortcut keys
  • Inforiver Reporting Matrix FAQs
  • Inforiver Writeback Matrix FAQs
  • Video tutorials
  • Resources
  • Release Notes
    • Inforiver Writeback Matrix May 2025 - v4.4.3
    • Inforiver Writeback Matrix April 2025 - v4.4.2
    • Inforiver Writeback Matrix April 2025 - v4.4.
    • Inforiver Writeback Matrix March 2025 - v4.3 - Time series formulae
    • Inforiver Writeback Matrix - March 2025 - v4.2
    • Inforiver Writeback Matrix March 2025 - v4.1
    • Inforiver Reporting Matrix March 2025 - V3.3.3 -New formatting and export options
    • Inforiver Writeback Matrix Feb 2025 - v4.0 - User permissions, High-performance mode, Input rules
    • Inforiver Writeback Matrix January 2025 - v3.9 - Fabric support, upgraded variables interface
    • Inforiver Writeback Matrix December 2024- v3.8 - Improved Scheduler Performance & Other Enhancements
    • Inforiver Writeback Matrix December 2024 - v3.7 - Merge rows, new rounding functions & More
    • Inforiver Writeback Matrix November 2024 - v3.6.2 - Import from Google Sheets & More
    • Inforiver Writeback Matrix October 2024 - v3.6 - Approval Workflows & Other Enhancements
    • Inforiver Writeback Matrix October 2024 - v3.5 - Lookup visuals & more
    • Inforiver Reporting Matrix Nov 2024 - v3.3 - Variables
Powered by GitBook
On this page
  • 1. Visual measure
  • 2. Visual column
  • 3. Row and column aggregation for formula measures

Was this helpful?

  1. Working with Inforiver
  2. 4. Adding business logic and formulae

Insert calculated columns

PreviousTemplate rowsNextInsert manual input rows

Last updated 10 months ago

Was this helpful?

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 for a detailed list of functions, operators, and identifiers that can be used for calculations.

1. Visual measure

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.

a) To insert a measure, select 'Insert Formula' from the 'Insert' tab. A side panel opens on the right.

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.

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.

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 to by using 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.

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.

f) Give the calculated measure a proper name, say 'Variance %', and click 'Create'.

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.

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.

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:

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.

c) Enter a title and the formula and click 'Create'.

d) You will see the values updated in the report.

Example 2:

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.

b) Enter the title and the formula. Click 'Create'.

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.

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., 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.

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.

Resources

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

b) You will also notice that the References section is a bit different. While creating a , 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.

You can view and edit the aggregation for formula measures from the Manage aggregation interface.

Learn more about aggregation features in Inforiver.
Insert Formulas, Columns and Aggregation
Visual calculations using Inforiver
Calculate remaining budget in Power BI (without using DAX)
Insert calculated measure or column in Power BI
new measure
formula syntax
Row aggregation type
Sales for 2021 and 2020 by quarter
Visual measure
Visual measure
List of references
List of functions
Reference list
Formula entered
Creating a measure
Measure inserted
Formula bar showing the calculation
Visual column
Reference list
Creating a visual column
Quarter on quarter column
Inserting a new column
Defining the calculation
Suppress calculation errors
Suppress calculation errors
Define custom error text
Using IFNA function
Row and column aggregation for formula fields