*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

**=
EXPAND(SUM([Count of Product]), ROWS)**

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

**=
EXPANDALL(SUM([Count of Product]), ROWS)**

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:

**=
CALCULATE([Profit], [Year] = 2013)**

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

**=
LOOKUP([Profit], [Year] = 2013)**

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.

## Be the first to comment