All language subtitles for 002 Calculation Groups (October 2023)_en

Instructor: In this video, we're going to cover

one of the exciting new features of the Model view

in Power BI, calculation groups.

Now, calculation groups in Power BI provide a way

to simplify measure management by grouping

common calculations into a single table structure,

which reduces the total number of measures

that are needed within your data model.

Now commonly, calculation groups are used

with time intelligence calculations,

currency conversion, scenario analysis,

and dynamic measures.

We're going to focus on time intelligence in this video.

But if you're interested in exploring

additional use cases and scenarios,

it's definitely worth checking out some

of these other common scenarios.

So setting up calculation groups is pretty straightforward.

And they can actually be created in desktop or service.

So from a high level, there are two main components.

We've got calculation groups and calculation items.

Now first up, you have calculation groups,

which is really just a collection of calculation items.

So instead of creating multiple measures

for calculations, like year-to-date,

quarter-to-date, month-to-date, year-over-year,

same period last year, et cetera,

so instead of having to create all

of the time intelligence versions

for each of those measures,

you can create a single calculation group,

and then define the items that belong to each group.

The other component are calculation items,

which are used to define the specific

calculations within a calculation group.

So each calculation item represents

a specific calculation or variation of a calculation,

and then are applied to the explicit measure

to alter that measures calculation.

We'll dig into exactly what this means in the demo.

But one of the really important pieces to note here

is that when you first enable

or first create a calculation group,

you'll get this notification

that implicit measures are discouraged.

And the reason is that calculation groups and items

only work with measures that are explicitly defined.

So this is really a great opportunity

to reinforce Power BI best practices

and create explicit measures against the data model.

So once we have our calculation groups defined

and the calculation items added,

we can now use these calculation groups

in our visuals where it makes sense.

So let's take a look at an example of a matrix visual

that is built out with uniquely created specific measures.

So as you can see here,

we have a base measure for total revenue.

And we've also built out five additional explicit

time intelligence measures, all based on revenue.

Now, I'm not saying that there's

anything wrong with this approach.

But as you continue to add more base measures,

like total profit, total orders,

we've got total revenue here, so on and so forth,

you're required to create new,

unique time intelligence functions for each base measure.

So needless to stay,

this really starts to clutter the Measures view.

And it can create a lot of individual measures.

In our second view here,

we've removed all of those individual unique measures

and have added in our time intelligence calculation group.

So what this allows us to do is to add

that same total revenue base measure.

But instead of adding five unique measures,

we're only adding in a single calculation group.

And despite the header naming conventions

being slightly different, the values are identical.

What's really exciting here is that we can

actually add in a field parameter

that contains a bunch of different measures,

and then use that parameter to dynamically update

the base value within the matrix, or the visual,

within that calculation group.

So it's seriously cool.

And we'll actually end up doing this as part

of our report page toward the end of this demo.

All right, let's head over to Power BI.

And we're gonna build out our calculation group.

All right, so I've already built out a matrix visual here

where we are looking at our category name.

So we've got mountain bikes and road bikes.

I've added in a date hierarchy

from the calendar lookup table.

So we have 2022 for January, February,

March, April, May, right?

So we're basically looking at 2022,

the first six months of the year.

And then, I have added in a bunch of different measures.

So like I said, I've got my total revenue base measure.

And then, I've also built out these five revenue-specific

time intelligence measures, right?

So previous month revenue,

total revenue month-over-month percent change,

total revenue month-over-month,

quarter-to-date, and year-to-date.

All right, so this is the formula that you would need

to follow if you wanted to create the same version

for things like profit, or sales, or order volume,

or bulk orders, or whatever the case is.

So what we want to do with our calculation group

is clean all of this up.

So in order to start building out calculation groups,

the easiest way that I found

is heading over to the Model view here.

And you can see within the Data Model view

we have tables.

And in the Model view,

we have this little calculation group option here.

So from here, we could right-click on the More Options menu

and define a new calculation group.

That's one way we could tackle this.

The other option is to come up here to our ribbon

and create a new calculation group from the Ribbon menu.

Either way, you're gonna end up in the exact same spot.

All right, so couple of things to notice here, right?

If we look at our calculation group, right,

we see that we have a one next to here.

So we've started to create a placeholder

for our first calculation group.

It's just named Calculation Group.

So it's gonna be important to update the name here.

And we've also started to create

the structure for calculation items.

And you can see here that we have our first

calculation item that's been defined.

And it uses this DAX function called Selected Measure.

And what Selected Measure does is it's just an expression

that is designed specifically for calculation items

and dynamic format strings to reference the measure

that is within the context.

So by within the context,

what they mean is that when you add

a explicitly defined measure to a visual,

that is the current measure within the context.

So any calculation items that are built out are

then going to be applied to that measure within the context.

All right, so for our first calculation item,

we're gonna update this.

And I'm gonna call this Current Measure.

Current Measure, all right?

So this is always just going to be

the version of the measure that is within the context.

So we'll hit Enter to lock this in.

And we'll see this calculation item name

update to Current Measure.

That looks great.

Can actually see it here as well within the Properties pane.

Now, if we come up to the calculation group

and click on this,

we can update the naming convention

used for this calculation group.

So let's call this something like Time Intelligence.

Again, you can add in your description, synonyms,

all sorts of stuff.

So now, we have our Time Intelligence calculation group.

And we've started to define our calculation items.

So from here, we can click on Calculation Item.

And again, you have this More Options menu

where you can hide, collapse, expand,

or add new calculation items.

When you click on this header in the Property pane,

this brings up an option to add

a new calculation item here as well.

So let's go through and start building

out some of our other calculation items.

All right, so up first,

let's build out a calculation item

to calculate previous month.

So we'll call this Previous Month.

And here, we're gonna use our Calculate statement.

So we wanna calculate the value of the selected measure.

We wanna do that in a modified filter context.

We're gonna do Date Add in a modified filter context

where we're gonna shift the calendar look up dates,

right here,

back one month.

All right, that's our period.

All right, so this is exactly how we would

build out any other previous month measure.

But instead of referencing a specific measure

or formula here within the expression

of the Calculate statement,

all we're doing is we're referencing

the selected measure value.

Again, that's the important piece to understand

here is that it's whatever that...

The measure is in the context of the visual

is going to be selected and applied for this calculation.

All right, so we've got our current measures,

our base, previous month.

Let's add in a new calculation item

for quarter-to-date and year-to-date.

All right, so this is going to be quarter-to-date

equals same start here with calculate,

and then selected measure.

And here, we'll use our dates, quarter-to-date function

with the date column from our calendar lookup table.

All right, and I'm going to copy this.

And I'm copying this because I'm gonna

update this for year-to-date.

It's basically the same exact format,

just instead of dates QTD, it dates YTD.

So we'll make that update real quick,

add new item.

We'll paste.

And then, update our name and our function,

dates YTD.

And then, we'll lock this in.

All right, perfect.

So there's two more calculation

items that I want to add in,

one for month-over-month,

and then another from month-over-month percent change.

So let's start off with our month-over-month value.

And I'm gonna use some variables as part of this,

just to kind of clean it up and make it readable.

So we're gonna start off with our kind of current value.

And this is just going to equal the selected measure.

The next thing that we wanna do is

we wanna to define the previous value.

And here, we're basically gonna calculate

our last month's version of the selected measure.

So again, we'll start off with our calculate

and then we're gonna use the selected measure,

our date add function.

And again, we're gonna take our calendar date,

our calendar date field,

and we're gonna shift our dates back one month.

All right, and then here for our return,

we basically just wanna subtract the current value

from the previous value.

Awesome, we'll lock this in.

Sweet, and then, the last thing that we're gonna do

is we'll create a item for month-over-month percent change.

And this is gonna be month-over...

Month-over-month percent

change,

and just using Alt + 30 on the keyboard

to go through and define that delta symbol.

But you can make that whatever you want.

All right, so again, here we're gonna start off,

we're gonna create a couple of variables

for our current value.

Again, this is going to equal the selected measure.

We wanna define our previous value.

Again, we're gonna calculate the selected value.

Excuse me, selected measure,

where our date add...

All right, we wanna shift our dates

back one month.

Close that out.

All right, and then, same thing.

We wanna create our variable here

for the difference of these two values.

So basically, we're just redefining

that month-over-month value.

Again, we could have copied and pasted this and updated it.

But it never hurts to work through the DAX.

Current value minus previous value.

And then, return.

We'll use the divide function.

And then, we're gonna divide our difference value

from previous value to get the month-over-month change.

Awesome.

All right, so we've got everything defined.

Let's head over to our Report view.

And I wanna build out a new visual.

So let's use our visual here as a base.

And I'm gonna copy and paste this, just to duplicate it.

And then, I'm just gonna shrink this top one

to give us a little bit of space to work with here.

All right, and I'll come in.

And I actually wanna remove all of these values.

So revenue on all of the time intelligence ones

that we built out, right?

So now, we're basically stuck, or left,

with this blank canvas.

So what I need to do here is add in my base value.

So let's use our same example.

And I'm gonna come back.

And I'm gonna add in total revenue.

So now, we're seeing our subcategories,

mountain bikes and road bikes,

for this first six months of 2022.

And we have our total revenue.

If you look at the matrix,

I know it's shrunk down a little bit above.

But you can see that we have 3.3 million

for total revenue for mountain bikes.

And the total grand totals are the exact same.

So far so good.

Everything's exactly the same.

What's really cool here is that

if we come into columns,

we can add in our Time Intelligence

calculation group to columns,

all of our items are added by default

into this visual.

All right, so we have our month-over-month revenue.

And if you look month-over-month to month-over-month,

these values line up.

If you look at previous month revenue

and previous month revenue,

all of these values align,

same thing with quarter-to-date and year-to-date.

So without having to add in all of those

additional time intelligence measures to this visual,

we were able to define it

and just add in that calculation group.

So couple of things here that are kind

of bothering me a little bit.

First thing is the order, right?

I'd like to be in that same exact order.

The other thing is the formatting

on the month-over-month percent change, right?

They're just showing up as dollars.

I'd like to see this as a percentage.

What's cool about the calculation groups is that

when you're referencing the base measure,

these different values take into consideration

the explicit version of that measure,

how it's formatted, and the data type within the model.

So kind of inherits that,

which is why we're seeing these

dollar amounts here for revenue.

So let's jump back over to the model.

And I want to take care of a couple of things.

First, the ordering, and then I wanna define the,

using this dynamic formatting string,

how we're gonna format the month-over-month

percent change measure.

So if we click on our calculation item header,

what's really cool here is this is all drag and drop.

So let's say we just want current measure, previous month,

maybe month-over-month change,

month-over-month percent change,

quarter-to-date, and then year-to-date, right?

So pretty happy with that order.

The next thing that we can do

is if we click on this calculation item,

and then toggle on dynamic format string,

and there's a bunch of different documentation

and help center articles around how to create

dynamic formatting strings.

There's different ways that you can approach

different types of calculations, and all that kind of stuff.

So depending on what you're trying to solve for here,

your solution may look a little bit different than mine.

But very simply, what we wanna do is we're gonna

use the pound sign, and then in decimal point,

and a pound sign, and then a percent sign, right?

So basically, we're saying,

"Hey, we've got two placeholders in front of the decimal,

and then we're gonna have our accuracy

to the hundreds place behind the decimal."

So it's pretty straightforward there.

You can expand this to include, you know,

larger values here, larger percentages,

like if you're getting into the thousands place,

stuff like that.

And then, from here,

let's jump back over to our Report view.

And we can now see that our measures

are all in the same exact order, top to bottom,

and our format for our percentages updated appropriately.

All right, so everything's looking really, really good here.

If we wanted to at this point,

we could actually go through and delete

all of these different measures, right?

We could remove these from the model.

We don't need them anymore,

unless we want to use these specifically

for other calculations, or something like that.

But again, it's a great way to go through

and kind of clean up a lot of the clutter

within your model, if it exists.

I'm gonna remove this top visual here for a moment.

And the last thing that I want to kind of talk about,

and I had hinted at this in the slides,

is that we can actually create a brand new field parameter

to toggle this current or base measure.

So let's go through and quickly set that up.

So we're gonna come up to Modeling, New Parameter,

New Field Parameter.

And I would like to call this

Measure Selector.

And all we need to do is add in the different

measures that we wanna add in here.

So I'm gonna add in total cost, total revenue.

Let's add in total profit, total orders.

And then, how about like quantity sold?

And I'll click Create.

So Power BI is automatically added

this parameter in as a slicer.

And when I select the different values within the slicer,

looks outta that,

see, nothing happens with my visual, right?

Nothing's updating or changing because what I need

to do is I actually need to reference this

parameter within my value section of the visual.

So what we want to do is pull out this total revenue

and update it with the Measure Selector.

So now, look what happens.

We've got total orders here.

You want the quantity sold as your base measure.

That'll update,

total profit, total revenue, total cost.

Again, it's amazing that you're able to now update

a couple of values within your matrix rule.

With a combination of something like this field parameter

and your time intelligence calculation group,

you're really creating a very dynamic visual here

that can help users and, you know,

different team members explore exactly what's going on.

All right, so at this point,

I think you're getting the idea of just how useful

and powerful calculation groups can be within Power BI.

And what's really cool is that with all

of the Power BI team's continued product development

and the evolution of calculation groups,

and items, and visuals,

all of this can be created from desktop,

or within the service environment.

So you've got a bunch of different

flexibility there as well.

All right, so have fun.

Explore this more on your own.

And try out some of the other use cases

for calculation groups.

I'll see you in the next lecture.

Until then, learn on.