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.