Power BI Blog: More on Visual Calculations Part 1

Welcome back to this week’s edition of the Power BI blog series.  This week, we take a deeper look at the new Preview feature, visual calculations.

Last week, we introduced to you the Preview feature: visual calculations.  Now, we plan to consider how to create a visual calculation and demonstrate a visual calculations specific function: RUNNINGSUM.

The RUNNINGSUM function is both useful and convenient.  In one of our earlier blogs on labelling growth on a Line chart, we created a cumulative measure for the profit data and used that to build visuals.  The DAX formula we used was:

Now using RUNNINGSUM, we can replace that with simply:

Much simpler!  We can also build other visual calculations based on the visual calculation Cum Profit – in our blog example, the subsequent chart measures could be built using visual calculations.

It is generally not easy to model running sums (cumulative totals) or moving averages in DAX – or even just to calculate the previous item of a field.  Now, with visual calculation specific functions like RUNNINGSUM, MOVINGAVERAGE, PREVIOUS or FIRST, it is easier to achieve that within visuals.  It is exciting to see how this feature will evolve for broader use than just confined in visuals.  Watch this space!

Before listing all the visual calculation specific functions, let’s look at some features, advantages and disadvantages of visual calculations:

• Visual calculations aren't stored in the model but within the visual, which means anything in the model must be added to the visual before the visual calculation can refer to it
• Compared to measures, visual calculations operate on aggregated data instead of the detailed level, often leading to performance benefits
• Power BI now displays a visual matrix for building visual calculations which makes the process very intuitive (in fact, we used to build similar matrices alongside when assembling similar visuals)

• Most of the existing DAX functions are available for building visual calculations, but since visual calculations work within the confines of the visual matrix, functions that rely on model relationships such as USERELATIONSHIP, RELATED or RELATEDTABLE cannot be used
• Templates are also provided to inspire visual calculation ideas

• Microsoft has tested and found the following visual types do not work with visual calculations:
  • ArcGIS Maps
  • Azure Map
  • Decomposition Tree
  • Key Influencers
  • Line and Stacked Column chart
  • Map
  • Metrics
  • Paginated Report
  • Power Automate
  • Power Maps
  • Python visual
  • Q&A
  • R visual
  • Shape Map
  • Slicer
  • Small Multiples
  • Smart Narrative
  • Treemap
• Visual calculations may be added and edited using Power BI Desktop; reports containing visual calculations can be published to the Power BI Service, but visual calculations cannot be edited in the Power BI Service
•  A visual calculation can't refer to itself on the same or different detail level
•  Power BI Embedded isn't supported for reports that use visual calculations
• The see recordsdrill-through functionality cannot be used on visuals containing visual calculations
• Users cannot set built-in or custom format strings or conditional formatting on visual calculations
• Users cannot set data categories or change aggregations on visual calculations
• Users cannot change the sort order for visual calculations
• Users cannot use field parameters with visual calculations.

Visual calculations offer great simplicity by calculating within the filter context of a visual and many of the visual calculations specific functions exploit that advantage.  Let’s take a look at the available functions as at the time of writing:

• COLLAPSE: calculation is evaluated at a higher level of the axis
• COLLAPSEALL: calculation is evaluated at the total level of the axis
• EXPAND: calculation is evaluated at a lower level of the axis
• EXPANDALL: calculation is evaluated at the leaf level of the axis
• FIRST: refers to the first row of an axis
• ISATLEVEL: reports whether a specified column is present at the current level
• LAST: refers to the last row of an axis
• MOVINGAVERAGE: adds a moving average on an axis
• NEXT: refers to a next row of an axis
• PREVIOUS: refers to a previous row of an axis
• RANGE: refers to a slice of rows of an axis
• RUNNINGSUM: adds a running sum on an axis.

We’ve touched on the RUNNINGSUM function, and some other functions like MOVINGAVERAGE or PREVIOUS work quite straightforwardly as their names suggest.  Next time, we’ll take a look at some of the more interesting functions.

In the meantime, please remember we offer training in Power BI which you can find out more about here.  If you wish to catch up on past articles, you can find all of our past Power BI blogs here.