All language subtitles for 005 The =Calculate Formula_en

In this lesson, I'd like to discuss a function called the equals calculate.

You're going to find that equals calculator is a really powerful dex function.

Well, we're going to be looking at here is how we can use it with some different filters.

So what I'm going to do is I'm actually just going to take this existing visualization, delete that

one, and let's just remove some of these fields.

So we just got our total cells.

So if we were to go into just saying having our country and our total sales value, let's say we wanted

to know by different business segments what the actual sales value was.

Now, one of the things that I could do is I could create a slicer.

And let's say, for example, we dropped our business segment in there.

You'll see that there's four different business segments.

So so for example, Archer's box, now you see straight away, obviously, that my total sales now gets

filtered by the box business segment.

So it's only showing me the values for that box segment.

But what if I wanted to actually have it as a column?

So I wanted to know what is the sales of my box?

Yeah.

Let's have a look and see how we could actually do that.

So just make that a bit wider and we're going to create a new measure for this.

So we're going to drop in a new measure.

And in this case, what we're going to call it is we're going to say that this is the sales of my box

equals.

Now, as I said, we've got a function called equals calculate.

So when I start typing, calculate, you'll see that calculate comes up as an option and I'm going to

select that.

And what it says to me is that, first of all, what is my expression?

Now, one of the really powerful things about calculate is that I can have different expressions in

there.

I could use a sum, I could use an average, I could use a min max account, a distinct count.

Basically you use any of the expressions.

So in one way it's working very much like an equal sum f or equals average it.

But the nice thing about it is that because you can actually just bring any expression in there, you

can tell it what you actually want to sum.

So let's say, for example, I wanted to say we're going to sum, and in this case we're going to say

we want to sum the sales.

And let's go down.

I'm going to choose data sales.

So that's my expression, is we could say some data sales.

When I press comma and then ask me what is filter one?

So now in this case, I wanted to go to the business segment and I want to find only where it equals

box.

Now, please note you can have multiple filters in there.

We will show an example of that.

But to start with, we'll just have one filter.

So in this case, I'm going to say go to the data table and we're going to look for our business segment

field.

So just look for your data business segment.

I'm going to select that.

And in this case, what we want to say is our filter equals.

And please, because it's a text field, we're going to use our quotations and we're going to say word

equals box.

Okay, before we move on with this, let's just review what we've got here.

We've got sales equals calculate the sum of our sales when the business segment equals box.

Press enter on that.

So we've created a new measure down here.

So we've got our measure.

Let's just drag that in there.

So now what you can see, actually, let's just do the formatting quickly.

Just remove those decimals, make it easier to read.

There we go.

So what you can see now is that cells box HD is filtered to only show the business segment bikes.

If I was to select this, you would see then the box would equal box.

So basically that would show that.

Also now if you change this, you will see that sales box continues to only show the sales box figures

because it's now got a filter that is basically overriding the filter here.

So it's showing what the cells of the box is.

Okay, so there we go.

That's how we get a filter on this.

So let's look at another example.

Let's say we've got our different years.

Now, remember, our years are actually in a different table, but we've got a relationship with that

table, so that's great.

You'll see that equals calculate actually can go across different relationships and work.

So now we want to say were the year equal to specific figure.

So let's actually just drop our slicer and drop a new slicer.

Let's have a look at these different years.

And just make that a bit smaller.

Drop a year and there.

And I'm actually going to make this a list.

Let's just go to options, go to our slice settings, and we're going to say that it's going to be a

vertical list.

So if I was to say I want anyone to see the data for 2019, now you can see that my table has been filtered

quite a bit, so it's showing me the sales of the bikes in 2019 and total sales as well for 2019.

So let's say that we wanted to say though, what was the sales in 2019 just as a new measure.

So we're going to add new measure.

And we're going to say that this is going to be our sales 2019 equals.

And use our calculate again.

Now you'll remember it in the previous one.

My expression was What is the sum of my sales?

However, we've actually already got a measure that does the sum of the sales.

It's called my total sales.

So if I press the left square bracket, I'll see a list of all my measures.

And instead of putting the expression, I can actually just put the name of the measure because that's

basically doing the sum of my sales.

So in this case, I'm just choosing the name of the measure and you can see it's in my square brackets,

but that's basically the sum of my sales and then my filter.

In this case, I wanted to go to the dates table and within the data table I wanted to go to the year,

select the year, and we're going to say equals.

Now, in this case, because here is a numeric field, I can actually just type it in.

I don't have to use the quotations.

Okay, So let's review that.

So here we go.

Sales 2019 equals calculate.

We're using the total sales measure, which is the sum of my sales filter is going to be date.

The year equals 2019.

Just enter a net sales 2019.

Just drop it in the table.

And there we go.

Actually did go in.

Nazi Germany had no cells in 2019.

So it's now done the calculation based across a table.

Yeah, let's just do the formatting on that quickly.

There we go.

Now what you will see is as you play more and more with equals calculate, you're going to see that

there's just more and more powerful things you can do with it.

The last one I want to show here is that you can create more than one filter.

So we may want to know what is the sales of box in 2019.

So now we want to have two different filters on the.

Okay, So let's create the measure for that.

Let's say new measure.

So we're going to call it in our sales box.

2019 equals.

And in this case, it's going to be calculate again.

Again, we're going to use the sum of our sales so we can use our total sales.

But as I say, please remember with these expressions, you could be using an average, you'd be using

a min, a max account, a distinct distinction.

You can use different expressions in here.

I've just used the sum just a bit simple, right?

We're going to go comma, we can move to first filter.

So in this first filter, we're going to our data table, we're going to our business segment.

We're saying equals.

And in this case we are saying equals box.

Now we can add another one and you'll see the full two two just gets added.

You could keep adding as many filters as you want, but in this case, we're going to the data table

and we're saying we're the year equals.

Now, remember, with this one, there's no quotation because we're using a numeric field and we're

saying with the dates, the year equals 2019.

And as you create more and more sophisticated Dak's, you're going to see that where it's highlighting

the parentheses will start to become important.

In this case, we do only have one set of parentheses, but if we did have an expression, we'd have

another set of parentheses.

So you've just got to make sure you've got a closing parentheses for every opening parentheses.

Okay, but there we go.

We got sales of the bikes 2019 and we basically going to accept that.

And let's drop that into our sales box.

Drop it in here.

And again, this is Phil to this.

Let's just format this quickly.

There we go.

I'll just accept all formatting.

And then you can see now that we've got the combination where we've got our sales bikes and it's now

calculating for each of the subregions.

Obviously this is also just working that we've got.

Subregion happens to be in our field at the moment and you could change this.

You could say, I want to see this by my different categories, for example, and it would obviously

then calculate.

Where it can.

For the different categories.

So you could use different options there.

Okay.

We're going to leave it there.

I'm going to conclude the lesson and I will see you in the next one.