Create visual calculations

• 7 minutes

Creating a visual calculation is straightforward - select a visual and choose New calculation. The visual calculation window consists of three major sections, as shown from top to bottom in the following image:

• The visual preview which shows the visual you're working with.
• A formula bar where you can add visual calculations.
• The visual matrix which shows the data in the visual, and displays the results of visual calculations as you add them. Any styling or theming you apply to your visual isn't applied to the visual matrix.

To add a visual calculation, type the expression in the formula bar. For example, the following code calculates the profit by subtracting Total Product Cost from Sales Amount.

Profit = [Sales Amount] – [Total Product Cost]

The following image shows this Profit calculation added to a matrix visual that contains Sales Amount and Total Product Cost by Fiscal Year. Now you can see the individual fiscal years and a total for the three different values: Sales Amount, Total Product Cost, and Profit.

By default, most visual calculations on a visual are evaluated row-by-row, like a calculated column. In the previous example, for each row of the visual matrix the current Sales Amount and Total Product Cost are subtracted, and the result is returned in the Profit column.

Although possible, there's no need to add an aggregation function like SUM as you would in a measure. In fact, it's better not to add such aggregates when they're not necessary, so you can more easily distinguish between measures and visual calculation expressions.

Hide fields from a visual

As you add visual calculations, they're shown in the list of fields on the visual and on the visual itself:

In visual calculations edit mode, you can hide fields from the visual just like you can hide columns and tables in the modeling view. For example, if you wanted to only show the Profit visual calculation, you can hide Sales Amount and Total Profit cost from view.

Hiding fields doesn't remove them from the visual or from the visual matrix, so your visual calculations can still refer to them and continue to work. A hidden field is still shown on the visual matrix but is not shown on the resulting visual. It's a recommended practice to only include hidden fields if they're necessary for your visual calculations to work.

Work with templates

Visual calculations include templates to make it easier to write common calculations. You can find templates by selecting the template button and choosing a template, such as:

• Running sum calculates the sum of values, adding the current value to the preceding values.
• Moving average calculates an average of a set of values in a given window by dividing the sum of the values by the size of the window.
• Percent of parent calculates the percentage of a value relative to its parent.
• Average of children calculates the average value of the set of child values.
• Versus previous compares a value to a preceding value.
• Versus next compares a value to a subsequent value.

Each template has a corresponding function which is added to the formula bar when you choose a template. You can also add your own expressions without relying on templates.

Available DAX functions

You can use many of the existing DAX functions in visual calculations. Since visual calculations work within the confines of the visual matrix, functions that rely on model relationships such as USERELATIONSHIP, RELATED, or RELATEDTABLE aren't available. Visual calculations also introduce a set of functions specific to visual calculations. Many of these functions are easier to use shortcuts to DAX window functions.