Calculated Measures

The calculated measures feature enables you to build reusable formulas and expressions using measures from your Spectrum and Vista data sources within Power BI reports.

The calculated measures tool is found in the top right corner of the Power BI report editor. Calculated measures can be used in Power BI reports across your organization, regardless of which report they were created in.

You can use this feature to combine existing measures with basic mathematical operators and a library of functions similar to those found in Power BI Data Analysis Expressions (DAX) reports. See the video below for an example of how to use this feature with Vista and Spectrum datasets.

In the Manage Calculated Measures window, select Add New to create a new calculated measure. For more information, see Create a Calculated Measure. Select an existing calculated measure from the Calculated Measures list to edit it. Select the trash icon next to an open calculated measure to delete it.

Functions

These are the functions available within the calculated measure tool.

ALL

Returns all the rows in a table, or all the values in a column, ignoring any filters that might have been applied.

ALLSELECTED

Removes context filters from columns and rows in the current query, while retaining all other context filters or explicit filters.

AVERAGE

Returns the average (arithmetic mean) of all the numbers in a column.

AVERAGEX

Calculates the average (arithmetic mean) of a set of expressions evaluated over a table.

CALCULATE

Evaluates an expression in a modified filter context.

CALCULATETABLE

Evaluates a table expression in a modified filter context.

COUNT

Counts the number of rows in the specified column that contain non-blank values.

COUNTROWS

Counts the number of rows in the specified table, or in a table defined by an expression.

COUNTX

Counts the number of rows that contain a number or an expression that evaluates to a number, when evaluating an expression over a table.

DATEADD

Returns a table that contains a column of dates, shifted either forward or backward in time by the specified number of intervals from the dates in the current context.

Note: This function is discouraged for use in visual calculations as it likely returns meaningless results.
DATEDIFF

Returns the number of interval boundaries between two dates.

DIVIDE

Performs division and returns alternate result or BLANK() on division by 0.

EARLIER

Returns the current value of the specified column in an outer evaluation pass of the mentioned column.

FILTER

Returns a table that represents a subset of another table or expression.

LOOKUPVALUE

Returns the value for the row that meets all criteria specified by search conditions. The function can apply one or more search conditions.

MAX

Returns the largest numeric value in a column, or between two scalar expressions.

MIN

Returns the smallest numeric value in a column, or between two scalar expressions.

RANKX

Returns the ranking of a number in a list of numbers for each row in the table argument.

SUM

Adds all the numbers in a column.

For more information about these functions, see https://learn.microsoft.com/en-us/dax/.