Skip to main content

Microsoft Power BI Updates Blog

Visual calculations (preview)

Headshot of article author Jeroen ter Heerdt

Senior Program Manager
20 February, 2024

This is it. The way you write DAX changes today with the preview of visual calculations!

Visual calculations are DAX calculations that are defined and executed directly on a visual. A visual calculation can refer to any data in the visual, including columns, measures, or other visual calculations. This approach removes the complexity of the semantic model and simplifies the process of writing DAX. You can use visual calculations to complete common business calculations such as running sums or moving averages. Visual calculations make it easy to do calculations that were previously very hard or even almost impossible to do.

To use visual calculations while in preview, you need to enable it in Options and Settings ➡️ Options ➡️ Preview features. Select visual calculations and select OK. Visual calculations will be enabled after Desktop is restarted.

Adding a visual calculation

To add a visual calculation, you first need to select a visual. Next, select the New calculation button in the ribbon:

This will open the visual calculations edit mode. This screen consists of three major parts. From top to bottom, they are:

The new calculation button is shown on the Home tab of the ribbon in Power BI Desktop in the Calculations group.

  • the visual preview, which shows the visual you are working with,
  • a formula bar where you can add visual calculations,
  • the visual matrix, showing the data in the visual. It will show the visual calculations as you add them.

The visual calculations edit mode consists of the visual preview at the top, the formula bar in the middle, which includes the button to add templates and the visual matrix at the bottom.

To add a visual calculation, type the expression in the formula bar. For example, in a visual that contains Sales Amount and Total Product Cost by Fiscal Year, you can add a visual calculation that calculates the profit for each year by simply typing:

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

The visual matrix is updated as you add visual calculations using in the formula bar. New visual calculations are added as columns to the visual matrix.

Additionally, you can easily add a running sum of profit by writing:

Running sum profit = RUNNINGSUM([Profit])

Here a visual with the two visual calculations we have just created:

A bar chart showing the profit and running sum example visual calculations as well as sales amount and total product cost by year.

By default, for most visuals, visual calculations are evaluated row-by-row, like a calculated column. In the example above, 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 is no need to like SUM as you would in a measure. In fact, it’s recommended not to add these aggregates when they are not necessary so you can more easily distinguish between measures and visual calculation expressions.

You can use many existing DAX functions in visual calculations. Functions specific to visual calculations are also available, such as RUNNINGSUM, PREVIOUS and MOVINGAVERAGE (see our documentation for a full list). Using these and other functions, visual calculations are much easier to read, write and maintain than the current DAX required. On top of that, most existing DAX will work, so this is not a brand-new language that you need to learn.

An easier and faster option for calculations

Visual calculations differ from the other calculations options in DAX in that they are not stored in the model, but instead on the visual. This means that visual calculations can only refer to what is on the visual. Anything that is in the model needs to be added to the visual before the visual calculation can refer to it. This means that visual calculations do not have to worry about the complexity of filter context and the model. Visual calculations combine the simplicity of context from calculated columns with the on-demand calculation flexibility from measures. Compared to measures, visual calculations operate on aggregated data, instead of the detail level, often leading to performance benefits. Since visual calculations are part of the visual, they can refer to the visual structure which leads to additional flexibility.

To learn more about the various options for doing calculations in Power BI, please see our documentation.

Hiding fields from the visual

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 just show the Profit visual calculation, you can hide Sales Amount and Total Profit cost from view:

You can hide visual calculations from the visual by using the icons on the field list of the visual.

Hiding fields does not 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 simply not shown on the resulting visual. It’s a recommended practice to only include hidden fields if they are necessary for your visual calculations to work.

Templates are provided

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 to work with:

Using the template button on the formula bar you can add templates to a visual calculation.

The following templates are available:

  • Running sum. This calculates the sum of values, adding the current value to the preceding values. This template leverages the RUNNINGSUM function.
  • 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. This template leverages the MOVINGAVERAGE function.
  • Percent of parent. Calculates the percentage of a value relative to its parent. This template uses the COLLAPSE function.
  • Percent of grand total. Calculates the percentage of a value relative to all values, using the COLLAPSEALL function.
  • Average of children. Calculates the average value of the set of child values. This template uses the EXPAND function.
  • Versus previous. Compares a value to a preceding value, using the PREVIOUS function.
  • Versus next. Compares a value to a subsequent value, using the NEXT function.
  • Versus first. Compares a value to the first value, using the FIRST function.
  • Versus last. Compares a value to the last value, using the LAST function.

Selecting a template inserts the template in the formula bar. These templates are just starting points. You can add your own expressions as well without relying on templates.

Axis determines how the calculation is evaluated

Many functions specific to visual calculations have an optional Axis parameter, which influences how the visual calculation traverses the visual matrix. This parameter is default set to the first axis in the visual. For many visuals, this is ROWS, which means that the visual calculation is evaluated row-by-row in the visual matrix, from top to bottom. The following are valid values for the Axis parameter:

Axis icon Axis name
The icon for ROWS is two downwards pointing arrows. ROWS Calculates vertically across rows from top to bottom.
The icon for COLUMNS is two horizontal arrows pointing to the right. COLUMNS Calculates horizontally across columns from left to right.
The icon for ROWS COLUMNS is two downwards pointing arrows connected with a dotted line from the end of the first to the beginning of the second ROWS COLUMNS Calculates vertically across rows from top to bottom, continuing column by column from left to right.
The icon for COLUMNS ROWS is two horizontal arrows pointing to the right, connected with a dotted line from the end of the first to the beginning of the second arrow. COLUMNS ROWS Calculates horizontally across columns from left to right, continuing row by row from top to bottom.

 

Restart the calculation with Reset

Many functions specific to visual calculations have an optional Reset parameter, which influences when the function resets its value to 0 while traversing the visual matrix. This parameter is by default set to None, which means that the visual calculation is never restarted. Reset expects there to be multiple levels on the axis. If there is only one level on the axis, you can use PARTITIONBY. The following are valid values for the Reset parameter:

  • NONE. This is the default value and does not reset the calculation.
  • HIGHESTPARENT. Resets the calculation when the value of the highest parent on the axis changes.
  • LOWESTPARENT. Resets the calculations when the value of the lowest parent on the axis changes.
  • A numerical value referring to the fields on the axis with the highest field being one.

To understand HIGHESTPARENT and LOWESTPARENT, take an axis that has three fields: Year, Quarter and Month. The HIGHESTPARENT is Year, while the lowest parent is Quarter. For example, a visual calculation that is defined as RUNNINGSUM([Sales Amount], HIGHESTPARENT) or RUNNINGSUM([Sales Amount], 1) returns a running sum of Sales Amount that starts from 0 for every year. A visual calculation defined as RUNNINGSUM([Sales Amount], LOWESTPARENT) or RUNNINGSUM([Sales Amount], 2) returns a running sum of Sales Amount that starts from 0 for every Quarter. Lastly, a visual calculation that is defined as RUNNINGSUM([Sales Amount]) does not reset, and will continue adding the Sales Amount value for each month to the previous values, without restarting.

Easier than regular DAX

We went to great lengths to make sure visual calculations are easy to use. They provide functions that are very easy to use with a low number of parameters to configure. Most of the time you will be writing DAX that almost feels like writing English.

Examples of these visual calculations specific functions are the RUNNINGSUM function shown above. Also, there is the MOVINGAVERAGE function and navigation functions like PREVIOUS and FIRST.

However, in some situations these functions might not do exactly what you need them to do. In this case, it’s good to know that the visual calculations specific functions are shortcuts to functions you might remember as the window functions that we added in December 2022. Specifically, OFFSET, INDEX and WINDOW. Those functions provide the highest level of flexibility but are not as easy to use as the visual calculations specific functions.

However, you can express the visual calculations specific functions in their equivalent window function to tweak the behavior to precisely what you want.

Let’s contrast visual calculations with measures by looking at an example of writing a running sum.

Here’s the regular DAX to write a running sum in a measure:

RunningSum =
CALCULATE (
    SUM ( 'Sales'[Sales Amount] ),
    FILTER (
        ALLSELECTED ( 'Date'[Fiscal Year] ),
        ISONORAFTER ( 'Date'[Fiscal Year], MAX ( 'Date'[Fiscal Year] ), DESC )
    )
)

Here’s how you would do write the same measure using WINDOW, one of the window functions:

RunningSumWindow =
CALCULATE (
    SUM ( 'Sales'[Sales Amount] ),
    WINDOW ( 0, abs, 0, rel, ALLSELECTED ( 'Date'[Fiscal Year] ) )
)

Here’s how you write it in a visual calculation:

RunningSumVisualCalculation = RUNNINGSUM([Sales Amount])

As you can see, the visual calculation is by far the easiest to read and write.

We need your help

We are only just getting started with this preview. There is a lot more that we have planned, so please stay tuned for updates in future releases. However, we invite you to jump in now!

For more information, including which functions are available, please read our documentation. Please try the preview today and let us know what you think.