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.