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.