Original subtitles

Welcome to this section on measures.

So we're going to be looking at how we can use measures in our calculations on Power BI.

You're going to see that using our decks is that we can create calculations in a different method to

what we've just been through in our calculate columns.

However, there's often confusion as to whether I should be using a measure or should I be using a calculated

column.

Because quite honestly, sometimes you can use either or and you would probably still get the right

result.

However, sometimes you can only use a measure to actually get the correct result, or you could only

use a calculated column.

So the reality is, like most things in life, this is something that you're going to have to practice.

You're going to have to get a bit of experience around and you're going to find again, I'm going to

reference Excel that you're going to see that it's going to be like Excel in terms of knowing when to

use which one.

So we're going to move into our measures.

So first off, you can see that I'm back in my data screen and I've chosen my data table.

Now, you'll remember that I created the calculated columns for my sales cost and profit.

And what it did was it basically created a new column and it created a row by row calculation of a result.

Now, the first thing to note is that a measure does not do this.

So basically a measure does not create a result within a table.

A measure always creates a result within a visualization.

So what we're going to do is we're going to start off by just using an example.

And what we're going to do is we're going to take this sales field.

And what we're going to do is we're going to then total this sales field.

We're going to get a total sales value.

So you can see our calculated column.

At the moment we've got sales and basically we got 60,919 results for that sales because it's bit by

bit calculation.

But let's go into our new measure, so please make sure you're picking new measure.

Please do not pick the new column.

And we're going to start off.

We're going to new measure.

And again, you're going to see that it works the same way.

We've got a formula bar just like we did with our calculator column.

It needs a unique name.

So we're going to say that this is going to be our total sales.

Again, it follows the same syntax where we put the name of the field and then we say, Well, in this

case it's actually more of a measure than a field.

So we're going to call it total sales, I'm going to say equals.

And what we're going to do is we're going to say that the total sales is the sum of the sales field.

Now, you can picture this.

If you had an Excel column and say, for example, you had a one to a 60,919, just like we've got here,

60,919 rows, if you were to go down to the bottom row and you were to say equal sum, a one to a 60,919,

it would then give you one cell that has the result of the entire calculation.

That's really what we're going to be doing.

So we're going to say in this case, we want to sum this.

So we start typing and you can see that there is this sum function.

You'll also see that there's some additional functions up there that if you use to excel, there's some

new ones there that you may not be used to.

Again, we're going to use our parentheses and we're going to open.

And you can see now that I get a list of all my actual field names.

But in this case, now I've also got the names of the table because I've got two tables that are in

my dataset.

So I'm actually going to go down and we're going to say that we want to use the data table and we're

going to use the sales field.

So again, you can see that the sales field is in the square brackets, whereas the table name is actually

not.

So the table name is just straight there data.

And then we have sales within the square brackets.

Okay.

So you can see we've got total sales equals the sum of the sales.

When I press enter, you'll now see that no new column has been created here.

Nothing new has been created.

However, in our data table you can see that we now have this new field called total sales.

You'll see that it gets a different icon.

It gets like a calculator type icon.

So this is telling me that this is actually a measure.

So when what does it mean when I say that the calculation is going to be done within a visualization?

Well, what it means is that at the moment, this total sales is actually doing nothing within my data

model.

It's not taking any space.

Whereas these calculated columns here are actually taking up space and resources because of the fact

that they're producing a result.

So let's jump across to the report and let's just see how this works.

So we're going to use a table again just to show this.

We've been using our subregion.

So our subregion is basically a list of all different countries.

Let's drop that in.

Now.

If I were to take my sales field, just the normal sales field that I've calculated, we know by default

what happens here is that it actually sums this up.

So it's summing up the sales.

So let's take on new field, which is our total sales field.

We drop that in and you'll see now that we get total sales and we get exactly the same result because

obviously it's doing the same calculation.

It's summing the sales because remember, it's equals the sum of the actual sales itself.

We get exactly the same results.

But one thing to note is that when we go to summer sales and you do the dropdown is you can change this

to an average, to a minimum, to a maximum to account.

However, if we go to the total sales, you'll see that we don't get those options.

So when you're working with the measure and we're working with the sum, all it can do is a some we

cannot change the method of aggregation because it's not working with the same level of detail.

What it's actually doing is it's now summing the cells within the visualization.

So it's looking at the data that's being brought into the visualization and is calculating according

to the results within this.

So in this case, it's now calculating for each of the sub region what is the sum of the sales.

So that's the first big thing to realize about a measure is that we're always calculating with the visualization.

However, you tend to find that measures can be much more efficient in terms of the calculations.

They tend to do a lot less calculations and therefore be much more efficient.

And also they're not taking up so much space in your data model because they're not being stored against

the actual table itself.

Also, once we've picked this, you'll see that we can format this just like any other field.

So if I pick my total sales, you see, I'll get now measured tools, but I get the same options.

So I say, what is the name of it?

I could change that.

Also, what is the home table now?

A measure that actually needs to be unique within your data model.

So that means that it can actually be stored against any table because it's a calculation.

It actually doesn't have to be against a specific table.

So in this case, we're storing against the data table because it makes more sense because my sales

are actually in the data table.

But you could store it against the data table if you wanted.

You could change this and store it, and it would still work exactly the way that you've seen at the

moment.

So Measure does not actually have to be linked to a specific table.

It can be linked to any table.

However, it does have to be linked to a table, so just take that into consideration.

And then the second part is when you're doing a calculated column is that the field name must be unique

within the table.

However, a measure must be unique within the data model, so you cannot have the same measure name

twice.

So just something to take into account.

Now you'll see that you get the same formatting options as we see in previously.

And also in this case, I can change this to 1000 separator.

It goes to decimal number and then I can now say that I want zero decimal places.

Hopefully you've sort of seen that how a measure is created.

I've given some context as to basically some of the rules around it.

Let's just create a couple more.

But I want to first create let's say, for example, we've got our total profit, so we want to take

this total profit.

We want to add up the profit.

But then what we want to do is we want to take the profit divided by our sales, and this gives me a

profit ratio.

So basically there'll be something that within a measure we can actually do because we're now going

to tell one amount to be divided by the other.

But let's have an example of that.

Now.

I can create a measure within my report.

So we're going to go up here, We're going to say we want to create a new measure, and in this case,

we're going to say that we want our total profit.

I'm going to say equals on this.

And again, we're going to be using awesome.

And again, we're going to now go down and we're going to use our data profit field to some.

So as you can see, we've got total profit equals the sum of our profit press.

Enter on that.

We've now got a new measure.

Drop that into our table.

We've now got total profit.

I'm going to remove this for summer sales.

Just take it out of there.

Just to show you the total profit as well.

You'll see that there's no methods of aggregation, but it gives us a totals.

Again, if you've got the total profit selected, could go up to your formatting and just remove those

decimals.

So there we go.

We've now got our total sums and not total profit.

Now, what we might want to do, as I say, is to create a ratio.

So we might want to say, how much is this as a percentage of this.

So we want to now take this figure divided by this figure.

Now what we can do is create another new measure, and we're going to call this our profit ratio.

And we're going to say equals in this.

And in this case, what we're going to do is we're going to use a Dak's function called divide.

I'm going to select Divide.

And you'll see at the top it says divide.

What is my numerator over my denominator?

So basically my numerator is the value on the top.

Now, in this case, I want to take my total profit.

And if you use your left square bracket again, what it will show you now is actually which measures

do you have available.

So I can actually use measures within other calculations.

So in this case I'm going to say I want to use my total profit comma, open up again, my total sales.

So this can actually make it really nice when you're doing your calculations to be instead of being

able to have to use expressions, you can actually use the actual measures within your calculations.

So let's see the result of that.

We've got our profit ratio equals divide, our total profit over our total sales.

Please note that you're using a comma here, not a divide sign, so just watch out for that.

So I'm going to press enter on that and we now get our profit ratio again.

You can see it's got that.

Drop it in there and you can see now that it's now calculated that as a ratio.

And again, with this, what I could do is I could set my different formatting so I could say I want

this is a percentage and we have one decimal and there we go.

We now go to our profit ratio.

Okay.

So we're going to conclude the lesson there.

In the next activity.

We're going to create some more of these measures.

Just to give you some examples of what we can do, I'm going to conclude the lesson.

See you in the next one.