Visual Calculations in Power BI

Visual calculations in Power BI are expressions that are created within the visual itself. Unlike traditional measures or calculated columns, visual calculations are specific to the visual you are working with and can be influenced by user interactions such as filtering and slicing. This makes visual calculations a flexible and powerful tool for customizing the way your data is presented. 

Here's an example visual calculation that defines a running sum for Sales Amount. 

Running sum = RUNNINGSUM([Sales Amount]) 

Visual calculations differ from the other calculations options in DAX: 

• Simplified DAX: Visual calculations refer only to data within the visual, reducing the complexity of the semantic model. 

• On-demand calculations: Ideal for business calculations like running sums and moving averages. 

• Not stored in the model: Visual calculations are stored in the visual, not in the model, making them simpler and more flexible. 

• Focus on aggregated data: Operate on aggregated data rather than detailed data, leading to potential performance benefits. 

• Greater flexibility: Can reference the visual structure, offering more flexibility compared to traditional DAX calculations. 

Once you enable visual calculations, you can: 

• Add visual calculations to your reports 

• Hide certain fields 

• Create visual calculations quickly using templates 

• Make flexible visual calculations by referring to the visual's axes 

Adding a visual calculation 

To add a visual calculation, select a visual and then select the New visual calculation button in the ribbon: 

The visual calculations window opens in Edit mode. The Edit mode screen consists of three major sections. 

• 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, in a visual that contains Sales Amount and Profit by Year, you can add a visual calculation that calculates the total Product Cost for each year by typing: 

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 only show the Profit visual calculation, you can hide the 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 isn't shown on the resulting visual. 

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

You can also create a templated visual calculation from the ribbon by clicking the bottom part of the New Visual Calculation button. 

The following templates are available: 

• Running sum: Calculates the sum of values, adding the current value to the preceding values. Uses 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. Uses the MOVINGAVERAGE function. 

• Percent of parent: Calculates the percentage of a value relative to its parent. 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. 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. 

For expert data solutions tailored to your business, contact us at Numlytics. Transform your data into actionable insights!