All language subtitles for 006 Visual Calculations (February 2024)_en

Instructor: In February, 2024,

the Power BI team introduced some brand new features

that aim to make DAX simpler

and more approachable for the average user.

Most notably something called visual calculations.

In this video I'll introduce

what visual calcs are all about, walk through some demos

and summarize the major pros and cons to be aware of.

Now, a couple important notes to keep in mind.

Number one, this is a high level introduction,

so we'll cover the basics here,

but save some of the more custom

or complex functionality for a future video.

And two, these are brand new as of February, 2024,

and still in preview mode.

So keep in mind that things are changing very, very quickly.

So with that, let's talk about

what visual calculations are all about.

Visual calcs are basically DAX calculations

that are tied to a specific visual on the report canvas.

They aren't added to your model

like traditional calculated columns or measures,

which means that they only exist

within the scope of that visual

and can't be used or referenced anywhere else.

So take a look at this matrix here,

where we're showing total orders by month and by year,

and we've got some additional calculated fields

like a running sum, moving average

and some comparisons between time periods.

Now you might notice that these feel really similar

to things like table calcs in Tableau

or pivot table value calculations in Excel.

But these are examples of Power BIs,

new visual calculations,

which are indicated by the icons that you see here.

And by editing one of these calculations,

you'll see that we have a whole new environment

inside of Power BI

where we can create and modify these visual calcs.

We've got a visual preview at the top,

and a calculation pane at the bottom,

which shows the DAX formula itself here in the formula bar

along with the underlying data matrix

that Power BI is using to produce the visual above.

Now in this case, it looks almost identical

since we're working with a matrix visual,

but this can be really handy

when using other types of charts and graphs

where the underlying data structure isn't quite this clear.

So let's talk about the major benefits

of visual calculations.

Number one, because they're stored on the visual,

they don't bloat the data model

with extra columns or measures.

This is perfect for cases

where you only need to show a calculated field in one place

or at one level of granularity.

They sealed significant performance benefits as well

because they can operate on aggregated data

compared to traditional calculated columns

which are defined and stored at row level granularity.

Number two, they're calculated using visual context.

In other words, the data that you see right in front of you,

which combines the simplicity of calculated columns

with the on-demand calculation flexibility of measures.

And this creates a more intuitive almost spreadsheet like

calculation experience.

And third, with the introduction of visual calculations

comes a brand new suite of templates and functions

that make writing and managing DAX much simpler.

And this is especially true

with things like ranks or running totals,

which can be really tricky if you're new to Power BI,

or if you're working with larger complex models.

So for example, if I click this FX icon

to the left of the formula bar,

you'll see we've got these prebuilt templates

for things like running sum, moving average,

percent of parent, different types of averages

or positional references.

And in this case, we're able to define a running total

using a single function and measure reference.

Running sum, total orders,

which is so much easier than writing a traditional measure

to do the same thing, which would look something like this.

So now that we have that context,

let's go back to our report view

and practice creating some visual calcs from scratch.

Now if you'd like to follow along,

I've added a new tab here called visual calcs

with three different visuals on the canvas.

We've got a matrix showing year and start of month

with our total orders measure on values.

We've got a second matrix,

which is showing the category name, subcategory name

and product with total revenue cost and profit measures.

And finally, we've got a bar chart here,

which is showing data broken down by occupation

with total customers here on the X axis.

And we'll start with our total orders matrix here,

because time intelligence

is one area where these visual calculations really shine.

So check this out.

If I select this visual,

we now see an option here in the home tab

where we can select new calculation.

And this is what we'll use to define our visual calcs.

So let's go ahead and give it a click.

That's gonna open up our new visual calculation environment.

And what I wanna do here is really just walk through

some of these prebuilt templates

to give you a sense of how these things work.

Let's start with a running sum.

You can see it drops in a formula

right here in the formula bar with a brand new function

that they're calling RUNNINGSUM.

This is an example of syntax sugar

or it's basically a shortcut

that hides a lot of complicated stuff

working behind the scenes

with window functions like index and offset.

Power BI is trying to make this as intuitive

and seamless for users as possible.

So it rolls up into this very simple function

with just a single argument, which is the field name.

This is where I can input any columns, or fields

or measures, or other visual calcs

that are part of this visual.

In this case, I can choose start of month,

total orders or year.

In this case, I want the running sum

or the running total of orders,

so I'll select that measure and press enter.

And there you go. Now we have a running sum,

which basically just adds up the total orders

and aggregates them into the total

all the way down to the grand total of 13,325.

Very, very simple, very straightforward stuff.

Let's try a moving average here.

Again, got another custom function introduced

with visual calcs called moving average.

This time we've got two arguments.

Again, we're gonna start with the field reference.

Again, total orders.

Now window size, this is basically

how many items we want to include in our average.

So we could start with something like two and press enter.

And what that's gonna do is basically just average

the previous two items at any view in our visual.

So for instance, 174.5 is the average of 184 and 165,

and so on and so forth.

This works at different layers of granularity.

So as you roll up or down,

those calculations still calculate properly

no matter what level of granularity you're looking at here.

So that one's pretty straightforward.

What if we wanted to change the window from two to three?

Well, we could head to our build to visual pane here,

click this right arrow, edit the calculation

and just swap the two for three.

Press enter, and we'll see our calculation change.

Next up, let's take a quick look at some of these

comparisons here versus previous, next, first and last.

We'll circle back to the percent of parent and children.

But for now, let's try something like versus previous,

this kind of positional reference here.

Again, very simple formula, just two arguments.

They both reference a field name

and as always, we're gonna reference total orders.

And all we're doing here,

is comparing the total order value against the previous one.

And like we talked about, this is really simple

when you're dealing with tools like Excel

but can be much trickier

to accomplish using traditional DAX,

this makes it much, much easier.

So total orders of 165 in February, 2020,

that's 19 fewer orders than the previous month.

198 is 33 more than 165.

That's what the versus previous calculation is doing.

And then very, very similar case for versus next.

And let's just show it just to be complete here,

total orders instead of referencing the last value,

this is just gonna reference the next value.

Again, this works even as you roll up or down,

or drill in or out of your matrix

it's still gonna calculate properly, which is great.

And then last thing we'll see is the versus first and last.

So same two arguments as always,

this should be familiar by now.

Total orders, total orders.

This one's gonna reference against the first visible value

in the visual in this case,

184 is the first value at this layer in the matrix.

So every other value is referenced against that 184.

And then of course versus last

is gonna do the exact same thing,

but instead of referencing the first value,

it's gonna reference the last visible value in the visual.

Let's go ahead and enter that in.

So now we're just comparing against 2056.

That's our baseline for all of these

versus last calculations here.

Now what's great about this is that

you might create visual calculations

as helper columns that contribute to other calculations,

you might replace measures

that you no longer need to show in the visual,

what you can do is update these show hide options

to determine exactly what's visible inside of your visual.

So for instance, maybe we don't need all of those

calculations we just created.

Maybe we only want the running sum, the average,

and versus previous.

We can keep these calculations here

but temporarily hide them from the visual itself.

So with that, let's jump back to our report.

You can see that our visual will update

with those new fields that we added, which looks good.

And now next up,

I wanna take a look at some of these other templates

for percent of parent, average of children,

things like that.

And to do that,

we're gonna take a look at our product matrix here.

And again, we'll go to home, new calculation

to get into our visual calc environment.

And it's worth noting that

we don't have to use the templates.

This is really just a collection of shortcuts

to make these calculations easy for everyday users.

But you can write your own custom DAX in here,

which is great if you need something a bit more complex

or a bit more custom.

In this case, suppose we wanted to calculate

a visual calculation version of profit,

which is revenue minus cost.

You can see we have the measure version of profit

already visible in our visual.

Could give this one a name like profit,

and just like any other calculated column, right,

we can reference the fields here like total revenue

minus total cost and press enter.

And that's going to add our visual calculation for profit

and you can see that our values match perfectly,

which is great.

And again, this can be really helpful for scenarios

where maybe we only need to show profit in one single chart

or we only care about profit at the product category level.

In those cases, visual calculations

could be much more performant and efficient

than defining a profit calculated column or measure

that evaluates at the row level granularity.

And one thing you might have noticed is the formatting

or lack thereof.

So we were able to format our profit measure

exactly as we wanted to.

In this case with a currency format.

We currently don't have the ability to do that

with visual calculations.

There's no formatting menu that we can access here.

So what we have to do, at least for the time being,

is force the format using a format function

inside the formula bar.

So lemme show you what that looks like.

We can edit the calculation

and all I'm gonna do is wrap that formula

in a format function.

And in this case we're gonna format it as currency,

which will look something like this.

And we're gonna round it down, we don't need decimal points.

So we'll use a kind of format string like this.

And now when we press enter, you can see those values update

and now it's formatted just like we had formatted

that measure version as well.

So suppose this matrix is really all about

profit calculations.

That case maybe we could choose to hide

some of these component fields.

We can hide the original profit measure as well.

And now we're left just with that profit visual calc

that we defined.

Next up, let's explore some of those other templates

that we haven't looked at yet.

Starting with percent of parent.

And this one looks a little bit more complicated, right?

We've got a couple different functions in here,

divide and collapse, which is a brand new DAX function

that you may not be familiar with,

but there's still only three user inputs

that we need to deal with.

And two of them are just referencing

the field that we care about.

Which in this case is profit.

So I'm actually gonna reference

the measure version of profit,

which I can even though it's not visible right now.

And I'll show you why in just a second.

So total profit, total profit.

Now the axis, this will be either rows or columns

depending on how our matrix is oriented.

And this case we have our product and category

and subcategory fields on rows.

So we'll use a rose argument here.

If we had transposed that

and put our product names on columns,

we could use columns as this argument instead.

But that should do the trick

if I go ahead and press enter,

could see we've got these percent values.

Again, not formatted by default,

but this is basically telling us for any given category,

what percentage does it represent

out of its parent values, right?

So if we look at subcategories first,

jerseys represented 48% of the total clothing profit,

which is its parent category.

Gloves represented 39%,

and caps represented the remaining 13%.

And then within those subcategories,

we can see the breakdown or composition of profit

at the product level, right?

So this short sleeve classic jersey

and extra large accounted for 11%

of the total jersey profit,

that's to interpret 8% of parent calculation.

And we could go ahead and format that one

just like we did with our last calculation.

We're gonna wrap it in a format function

and this time we could use something kind of like this.

We'll round it off, add a percentage sign, and there we go.

That's a bit more readable.

Now next up, let's look at the percent of grand total.

This one's pretty intuitive,

but again, uses some brand new functions like collapse all.

Let's go ahead and reference the total profit

measure again here.

Total profit, total profit.

Again, we're gonna deal with rows as our axis,

and let's just get ahead of it and add our format.

While we're thinking of it,

we're gonna wrap all of that in a format function.

And let's use that same percent format

like we did before, like so.

So now we have percent of grand total

instead of percent of parent.

And the difference is that now at every single level,

whether it's subcategory or product,

we're basing that percentage calculation

against the grand total value.

So now the difference is that

that same short sleeve classic jersey

that made up 11% of jersey profits,

now we're seeing that it only makes up 5%

of the overall profits.

So similar calculation,

just a slightly different perspective,

different way of looking at profit performance.

Alright, so last but not least,

let's take a look at this last one, average of children.

This is gonna use an expand

and an average function here, just two arguments.

You can see this is super easy, just field and access again

and this time instead of the measure version of profit,

I'm gonna point to the visual calculation version of profit.

And again, I'll show you why in just a sec.

Axis is gonna be rows.

Let's go ahead and lock that in.

And what happens?

Well, we get an error. It says we can't display the visual.

And if you read the error line here,

it says the function average cannot work

with values of type string.

So let's go back and edit this calculation.

You might be wondering, well,

what the heck we're averaging profit,

which is a numerical value field, right?

Well, the catch is that when we included

that format function,

we actually turned our profit visual calc into a text string

and text strings can't be averaged.

So there are two things we could do to fix this.

We could either remove the format statement

from our profit calculation,

or we could reference the total profit measure

which is properly formatted as a value.

So we kind of have to make a little bit of a trade off here.

In this case, let's go ahead

and reference the measure version and press enter.

And when we do that, all is well

and we get the proper averages here in our visual preview.

And to interpret these averages of children,

basically we're looking at the average

of the next layer of granularity.

So when we look at the clothing line,

which is the highest level of granularity

here in this visual,

we are averaging each subcategory.

So the profit for jerseys, gloves and caps

averages out to 31,244.

And then within those subcategories we're averaging out

the product level profits that they contain.

So let's head back to our report.

Last thing I wanna show you is a non matrix example.

Here we've got a bar chart

that's showing our total customer count by occupation.

And by default we're using our total customer measure,

which is a count of customers.

But suppose that instead of showing the counts here,

we actually wanted to modify this visual a little bit

and actually turn this into a percent of total visual.

Now I could start defining a whole bunch

of DAX functions using calculate all and divide,

add a bunch of measures to our models.

But this is actually a great use case for visual calcs.

So let's jump into our calculation pane here,

and let's add a calculation for percent of grand total.

And the field that we're dividing is total customers

and we're gonna collapse total customers.

And we are dealing with data on rows here,

which we can see in our visual matrix preview.

Let's press enter.

Now by default, it's going to show

both of those fields in the same visual,

which doesn't make sense

they're on totally different scales.

And just like before, we don't really need to show

the volume metric anymore,

we just wanna visualize this as percentages

so we can hide that original measure from the visual.

And let's jump back to our report now.

And now we're looking at just those percent of totals.

We could format those as percentages as well,

but we could also do some cool things

like we could sort based on those grand totals now.

And if we wanted to do a little bit of formatting,

maybe we add some data labels,

and we put 'em inside of the ends of the bars.

And now we've got a visual that shows

the percent of total customers broken down by occupation

and we didn't even have to add a single new measure

to our model.

So really nice common use case

for when visual calculations can really come in handy.

So now that we've seen visual calculations in action,

let's recap some of the major pros and cons.

Now on the pro side,

this new concept of visual context is a game changer.

It makes DAX simpler and more intuitive,

especially for large models or complex calculations

that require positional references.

Things like ranks or running totals

that traditionally are quite challenging to define

using normal DAX measures.

Visual calculations can also be quite a bit more performant

than traditional calculated columns

because they can be applied at aggregated levels

or to specific individual visuals.

And as a result, you can avoid model bloat.

They reduce the need for

extra unnecessary calculated columns and measures

in the semantic model.

And again, this is ideal

when you just have a metric that you wanna show

in one chart, in your report

or at one specific level of granularity.

One of the nice things about visual calcs

is that they include all of those prebuilt templates

and the supporting function set as well.

Functions like EXPAND, COLLAPSE, RUNNINGSUM,

FIRST, NEXT et cetera.

This makes it so much easier to use these functions

compared to the underlying window functions

that are actually driving the calculations.

And one thing that I find super helpful

is the visual calculation editor

and how it shows the exact data structure

behind a specific visual on the canvas.

This is really helpful,

especially for brand new Power BI users.

And then last but not least,

love that visual calcs allow you to customize

which calculations or fields are shown or hidden

inside of a visual.

That level of flexibility is a really nice touch.

Now on the con side.

For one, this does create a pretty fragmented DAX authoring

and editing experience.

There are now three or four different places

where you can write DAX code inside of Power BI desktop.

And the challenge is that the functionality

is kind of inconsistent between them.

So there are certain DAX measures and functions

that might work in one place but not in another.

And trying to find and edit those visual calculations

can be a little bit tricky at first.

Another con is that these calculations

are isolated to specific visuals,

and you can't use them or reference them elsewhere.

In fact, at this point

I don't even think you can copy and paste

visual calculation code between different visuals.

So you do end up having to create these visual calcs

from scratch each time you need to produce them.

Now because these are brand new,

they're currently only compatible

with a subset of basic native visuals.

At this point, they don't work with maps,

they don't work with combo charts,

they don't work with custom visuals.

And like we showed,

there are pretty limited formatting, filtering

and sorting options here.

You can't use conditional formats,

you can't use data categories,

you can't even use standard formatting options.

You need to use a format function

to kind of force these visual calculations

into the formats that you're looking for.

I do expect this one to get better over time

as we get closer to general availability.

There's also quite limited functionality

as far as Power BI service is concerned.

So you can't edit visual calcs in service.

You also can't publish reports to web

that use visual calculations.

And then last but not least,

remember this is still a preview feature,

so documentation is limited,

and features really do continue to change pretty frequently.

Now if you'd like to learn more about visual calcs,

head to learn.microsoft.com,

and here you'll find some really helpful resources,

including this article here about visual calculations

that talk about how to enable them.

Talks about the editing pain,

talks about how to hide fields, how to use the templates.

These are the different access options that we talked about,

rows and columns.

And then one thing that's super helpful at the bottom,

these are all of the new available functions

and what they do,

and then there's a whole kind of comprehensive list

of current limitations as well.

So there you have it.

That's our overview of Power BIs new visual calculations,

hope you found it helpful,

and if you've taken some time

to play with these on your own,

go ahead and draw me a comment

and let me know what you think.