Power BI Blog: More on Visual Calculations Part 2

Welcome back to this week’s edition of the Power BI blog series.  This week, we take a deeper look at some more of the visual calculations available in this exciting new preview feature.

Recently, we introduced to you the Preview feature: visual calculations.  Last time, we demonstrated  how RUNNINGSUM  could help us, and went on the look at some of the advantages and disadvantages when using visual calculations. 

We left you with a list of the visual calculations available 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.

Some functions, for example MOVINGAVERAGE or PREVIOUS simply provide the functionality suggested by  their names.  However, visual calculations provide more level-flexibility by using the functions COLLAPSE / COLLAPSEALL and EXPAND / EXPANDALL, so let’s take a closer look at these.

The function COLLAPSE evaluates a calculation at a higher level of the axis and the function COLLAPSEALL evaluates a calculation at the total level of the axis.  The function EXPAND evaluates a calculation at a lower level of the axis and the function EXPANDALL evaluates a calculation at the leaf level of the axis.

In the following example built on Power BI’s default ‘Financial Samples’ dataset, we created a matrix with the field Profit listed over months:

If we apply the COLLAPSE function on Profit we will evaluate it at a higher level – in this case, the yearly subtotals.

Let’s consider another example on distinct counts of product types with the same dataset.

There are six [6] types of products in the dataset, and the subtotals and the overall total show that the distinct counts are always six [6] either within each year or over the whole dataset.  However, if we apply the EXPAND function

we will observe something interesting:

Here, the subtotal rows evaluate the distinct counts at a lower level each month and then sums for the years.  For example, each month in the year 2014 has six [6] types of products, and the 2014 subtotal sums up 12 months to have (6 x 12) 72 types.  However, the overall total only evaluates one [1] level lower, to each year, and obtains six [6] distinct types for each year and a sum of 12 in total.

If we instead apply the EXPANDALL function, the overall total will evaluate down to the lowest level, to each month

and show us a sum over all 16 months:

Model relationships are generally not available in visual calculations except using the specific functions.  For example, for the earlier example on Profit, we are not allowed to apply filters in calculations like this:

However, we do find exceptions to that.  For example, the function LOOKUP can still be used with filters in visual calculations, and we are allowed to do something like this

to build on which, we can even calculate year-on-year growth:

The divide-by-zero errors can be cleaned by IF functions, but you get the idea.

Visual calculations is still a Preview feature for Power BI, as Microsoft continues to improve and update the feature.  For example, there are small glitches like this:

When a field used in the calculation is renamed, it will not be updated simultaneously in the visual calculations Formula bar, but shows red underline instead.  Here we are changing ‘Sum of Profit’ to ‘Profit’ for demonstration purposes.  It is only refreshed when the Edit mode screen is closed and re-opened.

It is worthwhile trying it out yourself, and we believe this can become a much more powerful feature as it matures.

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