Creating customized calculations in PowerReports

Edit online

Use the visual calculations function in PowerReports to create calculated fields based on data that is available in a visual.

About this task

A visual calculation is a Data Analysis Expressions (DAX) calculation that is defined and run directly on a visual. Visual calculations make it easier to create calculations that were previously difficult to create, which results in simpler DAX, easier maintenance, and better performance. While you edit a PowerReport, you can access the visual calculations interface to create data fields by using flexible and powerful DAX calculations.

Note: The calculations are not added to the data model and new fields are not created in the fields list. The fields are available for use only within the visual where they are created.

For more information about DAX measures and visual calculations, see the Microsoft visual calculations documentation.

Procedure

  1. Click the More options button, which is represented by three horizontal dots on any visual, or right-click a visual while editing a PowerReport.
  2. In the menu, click New visual calculation and choose one of the following options:
    • Choose a predefined base calculation.
    • To begin writing your own calculation, click Custom.
    The visual calculations panel opens and displays a view of the current visual, a formula entry field, and an example of the available data.
  3. Enter a name for the field and enter a formula in the entry field.
    All text that precedes the = symbol is the name of the field.
  4. Press Return to apply the calculation and preview it in the visual panels.
  5. To hide a calculated field from the visual display, in the Visualizations tab, click the field's icon to show or hide in visual.
    The field is removed from the preview while the information is retained in the raw data table. You cannot hide raw data fields from the dataset. Use this option if you require multi-step calculations without showing intermediate data.

Example

The following examples are specific to IBM® ESG Suite and show calculations that you can create in reports. Each example shows a description of the formula, followed by a working example that uses fields from the monthly dataset.

Not all of the following calculations are effective in all scenarios, and some modification might be required to meet specific needs. All calculations require that the visual contains all the fields that are required to return a result. Sometimes, you might need to place a field at the end of a series of nested fields:

Return results only from a specific category of data
  • Field Name = LOOKUP(SUM([Data Source Field]), [Criteria Field], "Criteria")
  • Scope 1 Emissions = LOOKUP(SUM([Emissions]), [Scope], "Scope 1")
Combine the results of multiple fields together, for example, to add, subtract or multiply
  • Field Name = [Data Source Field 1] + [Data Source Field 2]
  • Lifecycle Emissions = [Emissions] + [Indirect Emissions]
Create a ratio or intensity metric
  • Field Name = DIVIDE([Numerator Field], [Denominator Field], “Result if blank or error”)
  • Cost per kWh = DIVIDE([Cost], [Energy in kWh], 0)
Show a single year of baseline data in a visual with multiple years of data instead of adding all baseline results together
  • Field Name = LOOKUP(SUM([Baseline Field]), [Period Criteria Field], "Period Criteria")
  • Baseline = LOOKUP([Native Units Baseline], [Year], "2025")