Original subtitles

In this activity.

We've got a few questions that our sales manager would like us to answer.

So I'm going to continue to use this table that we were using previously.

I'm just going to make a bit wider.

We're going to be adding a few calculations in here.

So I just want to take you back, though, to the data model before we move forward.

I just want to say again, please note that these calculations are not adding any new data into our

actual table.

So when you're creating a calculate column, which we did in the previous section, it creates a new

column and creates a result per row.

When we're doing the measure, what it's doing is it's creating a calculation that is being performed

within the visualization.

So it's not adding anything to our tables.

All it's doing is actually adding a new calculation here.

Please note that.

Okay, so we're going to look at the first one.

It wants to know what is the total order quantity.

So again, we can create a new measure on this.

Just click on new measure and we're going to say this is our total order quantity.

And again, we're going to be saying equals.

And in this case we're going to continue to use sum.

So again, we're going to be opening our parentheses.

And in this case, we want to take our data table and our order quantity field.

We're going to select those two and basically just have that been some press enter on that.

And again, now we find that we've got a new field, total order quantity, drop that in there.

And again we have we got this selected as our field.

We could actually then just select all formatting and you'll see now that we've put 1000 separator in

there, the next one is our average sales.

So what you're going to see is just like Excel, we do get the ability to use our different methods

of aggregation be equal, average equals min equals max.

And I show a couple of examples of that.

Right?

So let's go to our new measure.

And in this case, it's going to be our average sales that we're going to be creating as a new calculation.

We're going to say equal to that.

And as we start typing in average as a function, you'll see that we get average, average, average.

So we get different option.

We're just going to use the average for this and we're going to be averaging our sales.

So we go down to our data or sales, select that.

Close up.

Parentheses.

Very important.

Press enter.

And again.

Now we get our new field average cells.

So we're going to add that to our table.

Okay, So there we go.

We've got it added to our table.

And again, we can do our formatting.

Just remove those decimal places.

And there we got our average sales field.

Okay, let's move on.

We want to know the highest sales.

So we're going to be using our equals max for that.

We're going to be looking for the highest individual sales transaction for each of the different countries.

So we're going to say new measure again.

So in this case, we're going to set highest sale.

And just remember when I was saying that earlier that when you're creating these measures, the name

you're using must be unique within the data model.

So you're going to find that naming of your fields will become interesting as your models become bigger

and bigger and you're creating more calculations.

So just something to be careful of.

So in this case, we're just using the equals max function.

We want to know what was the maximum sale.

And again, we're going to be looking at odd data fields.

So we're going to say equals max.

So we're going to say in this case, we're going to look for data.

And you can see as I started typing day, it takes me to my data table and we can go down to our data

sales and we're going to be saying that our hi sale is the max of our sales.

Again, press enter on that, get our highest sale, drop it in there and again, I can do my formatting.

Fix that up and then I'll get each of the country's highest sale been displayed.

So the highest sale, the lowest sale will obviously be our men.

So we're going to now again create a new measure and we're going to say our lowest sale.

Equals.

In this case, it's going to be all men.

And again, we're going to be going to our data table, go down to our sales.

Select that.

So again, in this case, our low cell will equal the min of our data table Sales Field.

Press.

Enter.

There we go.

Got our new calculation.

Lowest sale pinching.

That's because it's pretty small.

So I'm going to actually leave the decimal numbers for that.

So they've seen that we can use our equals average equals min equals.

And the next one wants to know the number of customers.

So what I've seen previously in the course that we have a function called equals distinct count.

So we're going to be using that for that.

We're going to count the number of customers that we have on our data table.

So we're going to start off with our new measure and we'll say in this case we want to go number of

customers.

I'm going to use our equals again.

And in this case, we're using a function called distinct count.

Again, as you can see, as I start typing this again, quite a lot of functions.

Please note, though, that we're going to use the distinct count.

Now, in this case, I know there's no blanks in my dataset, but you may want to use blank.

No blank.

If you do have the possibility of nulls or blanks within your dataset.

I'm going to use distinct count function, and in this case we're going to be counting the number of

customers.

So we're going to go to the customer field.

So we're going to go to the data table and we're going to choose our customer field.

Close up parentheses on that.

Again, press enter and drop that in there.

And you can see that we now have the number of customers.

So not entire dataset.

We have 633 unique customers and then it's showing us for each of our subregions how many customers.

So our last one is what is the average sale per customer?

So this is very much like a calculation that we did in the previous lesson.

What we want to do is we want to take our total sales in this case though, and divide it by our number

of customers.

So we're going to use the same function that we did in the previous one.

We're going to use a divide function.

Now, please note that a divide function is is a really nice function within index, because what happens

is that if you get a divide by zero error, it actually just gives you zero as the response.

And you can actually change that.

If you want a different response, you can actually change it to a different one.

But most of the time if you get to divide by zero, if you give zero as your actual answer, to be honest,

that's normally appropriate.

So it's nice that it covers that that option that you can have.

So we're going to say new measure for this.

And in this case we're going to call it average sale per customer.

And we're going to say equals and again, we're going to use our divide function.

So we're going to use divide.

Bring it up again.

There we go.

And again, you'll remember that the first part is our numerator.

Now, when I press my left square bracket, you'll see that I've got a lot more measures that have been

created.

Now as we've been going along in this this activity, we've been creating new ones.

We still, though we want to use our total cells as our numerator, comma.

And again, I'm going to press the left square one, and we wanted to divide by the number of customers.

So we're going to select a number of customers.

Close our parentheses in this case.

And let's press enter on that.

And you'll see that we've got our new calculation there.

So it's just like that.

Just click on it.

And there we have our average sale per customer.

And again, we could just do our formatting.

And that.

So as you can see, once we're creating these calculations, they're now being used within the context

of your actual visualization.

So again, if we go back to our data model, you'll see that they have not been added into the table

itself.

They're just a calculation that can be used.

So if we created now, for example, a new visualization, let's say another column chart and let's

say you wanted to know what was the average sale for each of your categories, for example.

So drop categories in there, use your average sale in your y axis.

You will see then that will be just calculated as any other calculation.

So hopefully those examples make sense.

We're going to conclude the activity there.

I'll see you in the next lesson.