All language subtitles for 002 DAX Calculated Columns_en

Welcome to the section.

In this section, we're going to be focusing on building our data model.

So we're going to be looking at the underlying data that is giving us the results.

So we've seen earlier on in the course that we can easily create visualizations to see the results of

our data.

So now we're going to look at how we can enhance our data and create these new calculations.

Okay.

So we're going to start off actually using a new data file for this.

Now, if you just start a new Power BI desktop, you will see that you do get import data from Excel

as one of the options straight on the screen.

So I'm actually going to select that and we're going to go jump straight into that.

Okay.

So once you've selected that, please navigate to where you've downloaded all the training data and

we're going to be using the file called training sample.

So if you can just select that file, click on the open button and again, you'll see that it will make

a connection now through to this data file and it brings up our navigator.

So again, we can actually see a preview of this data.

Now, remember, when we see the blue line on top, that means that it's a table of data versus if we

see the little tabs, that means that it's a sheet of data.

So again, we're going to use the table of data, we're going to select that.

And we can see we're going to preview the data look pretty good.

Now, in this case, again, we're going to be using the load button.

So we're just going to click on load and we're going to select that.

Okay.

So yet again, our data is now going to be loaded into our Power BI desktop.

Here we've got the data loaded and we can see on the right hand side that we've got a new set of fields

and you can see that we've got a table and we've got these different field names that we're going to

be using.

So as I said in this section, we're going to be spending a bit more time on the data model.

So what I'm going to do is actually jump across.

We're going to go to our navigation on the left hand side.

We're going to click on data and we're going to bring up that spreadsheet view that we saw earlier on

in the course.

And we're going to see a few things that we can actually do here.

Now, when you go across to the right hand side, you will see that when you actually right click on

one of the fields, that it actually gives you a number of options that you can use.

It gives you your context sensitive menu.

So you can see that we can create things called new measures, new columns, new quick measures, refreshed

data, adding query to a whole bunch of different options that can do.

Also, you'll see at the top that we do get now a new menu.

In this case, it's showing us the table tools.

And what you'll see is that my table name is actually just called table one, so I'm only going to change

the name of that and we're going to call this at our data table.

So I'm going to just change the name press enter and you'll see straight away that that now has changed

the name to data.

Okay.

So that's just how we change a table name.

You could go ahead and you could change any of these field names just as easily.

So clicked on this one could come in here and you could change the name if you wanted to.

Now, in this case, we don't have any need to, but if you wanted to, you could.

Okay, So we're going to accept that data.

We're going to start off with the data.

Now, what we're going to be looking at is how do we create a calculation within this?

So if we look at the data itself, you'll see that we have a list price and we have an order quantity.

Now, if we were to take our list price and we were to multiply by our order quantity, that would give

us what our sales amount is.

So we're going to create a new column.

And what's going to happen is you're going to see that a new column is going to be created on the outside

over here.

And what it's going to do is it's then going to compute a result for each row in the table.

So this is actually called a new calculated column.

And you can see at the top under our table tools that we do get the ability to be able to create this

new column.

Okay.

So we're going to select that and say we want to create a new column.

And you can see now that the column has been created on the outside.

And at the moment it's just got the column name.

You can also see that we've now got our formula bar and it's got the word column equals in there, just

like you would with Excel.

It's a similar, very similar to using the formula bar in Excel.

Now when we're creating a new column within Power BI, what we need to do is we need to give it a name

so every field always has a name within power.

So just because we're creating a new column here, we're going to say we're going to call it sales.

And then you always start your formula by saying equals.

So again, very similar to your Excel where we're using our equals.

Now in this case you can have spaces between your equals or no spaces.

It doesn't really make any difference.

I'm going to put a space in just so that it's easier to read.

Now what we want to do in this is we want to actually say that we want to take our list price and we

want to multiply it by our order quantity.

Now, when it comes to choosing a field, we can use the left square bracket.

So when I press the left square bracket, you'll see that a list of fields actually appears on my formula

ball.

So all it did press the left square bracket.

Now, if I want to go to all the fields that begin with l, I just press the L and you can see now it

shows me only one field, which is my list price.

So I can actually now select that by just clicking on it and it will now actually put the list price

in there.

So we're saying sales equals the list price and we need to tell it to multiply.

Again, I'm going to use a space.

You don't have to use a space.

So we're going to use our asterisk as our multiplication sign.

And again, now I want to now say it's going to be multiplied by the order quantity.

So again, now I could use the left square, select that.

And in this case, it's going to be all the quantity I can press.

Oh.

And you can see now it's only showing me the order quantity field.

So I'm going to select that.

So you can see that this formula is very simple.

It's just saying sales equals our list price multiplied by or the quantity.

When I press enter on this, you'll see now that the calculation is now done row by row.

And this is really the big difference that we're going to see, that when we're creating a new column,

you're always creating a column within the table and it will always produce a row by row result.

So we can go across to each one of these rows and we could see what the list price is multiplied by

the quantity and see what the result of this is.

The moment you can see that the amounts are all the same, but it does change later on down the data

set.

So we've now got this new field called sales.

Now you'll remember that we can actually use this field in our reports.

And the beauty of this is that it's just used like any other field.

So if I go back to my report and we say, now we want to build a new table, so we choose our table

visualization, select that.

And in this case, what we're going to use is we're going to use a subregion.

A subregion is actually a list of countries.

So that's there's a subregion.

And now you'll see that my sales field is now being in my list of fields.

So if I want to use that, I can select it.

Just one thing to note, it does get a different icon.

So that icon is actually showing me that it is a calculated column, so it's not native to the table

like these other fields were.

But when I drag this in, you'll see that automatically the system now do the calculation and you can

see that we get the results of the totals.

Now, one of the things that you might notice straight away is of formatting.

We got all of these decimal numbers.

Now when it comes to formatting, this works exactly the same as it did earlier on in the course.

If I select this field, you'll see now that I can change could actually change the name of it.

It changed the data type formatting of it.

You see, you get different types of formats that you can use, but you'll remember with your formatting

we could use 1000 separator.

So I'm going to select that with my comma and now it gives me two decimal points.

I'm going to say I want zero decimal points.

And as I say, just like earlier on in the course, now that it has created this as the default for

that sum of cells.

You'll also see that if I go here and I click on the dropdown, that I get the ability to change my

method of aggregation so I can do my average, my min, my max.

So if I said what is my minimum sales amount, show me my minimum sales amount, I go to my average.

So this works again, just like it would exactly earlier on in the course with our fields.

So just one other point to note out as well.

With the sales field at the moment, it's said to be the same as the default summarization.

So just remember, if you wanted to change this to another method of aggregation, you could do that

as well.

Okay.

So I'm going to change this back to some of sales.

And now we've got some sales there.

Let's go back to a data model and have a look at our data.

Now, let's say I wanted to know what was my total cost.

And you can see I've got my unit price and I've got my order quantity.

And basically I would do exactly the same format.

Now, what I could do is I could use new column from this option in the ribbon, or I could go across

to my table, I could right click on this and actually, let's choose the three ellipses.

There we go.

The three ellipses, you'll see that you get a new column option so you could use either or.

Now, in this case, I'm going to choose new column.

Now, please make sure at this point, remember, we're creating a new calculated column.

Do not choose new measure.

We're going to do that in the next section and you're going to see something quite different.

So we're going to click on new column and again, we're going to get our formula bar.

And in this case, we're going to say we want to know what our total cost is.

I'm going to say equals.

Now, in this case, we want a unit price.

So again, I'm going to press my left square bracket.

I'm going to press you.

And it will show me that I have my unit price field.

And in this case, we're going to be saying, I want to multiply that again f square bracket by my order

quantity.

And you can see once you get the hang of this, it's pretty quick and easy.

So there we go.

We got our cost equals the unit price multiplied by the order quantity.

Again, I could accept that.

And from here now, I could actually change the formatting.

So I've got the cost field selected.

You can see it's highlighted over here and we could just go into a formatting and we could change the

default there.

And you can see that it's updated at U on this model again.

Now if I went back into my report, I can just choose my cost Now drag that into the visualization and

there we go.

You can see that the cost has been added.

Okay.

One last one that we're going to look at in this lesson.

So we're going to go back to our data.

Now, let's say we wanted to say we want to know profit.

Now a profit would be our sales minus our cost.

Now, these are two calculated columns, but we can use them actually in other calculations as well.

So I could create a new field, which I'm going to do now called my profit and say, I want to take

my profit, which is my sales minus my cost.

So again, let's do that.

In this case, I'm just going to use this option to create the new column.

So I'm going to say new column in this case.

Again, you can see that it's created column and we're going to say that this is our profit field and

be equals.

Now, again, when I press my left square bracket, you'll see that sales is now one of the options

that I've got to select because it's now a field within my table.

So I'm going to select sales.

I'm going to say that we want to minus again.

Now, when I look here, I'll see the cost is actually a field as well.

So now I can say profit equals my sales minus my cost.

So we're going to accept that.

Press enter.

We now go profit as a new field.

And again, I could do my formatting thousand separator, no decimal places, go back to my report.

And now by each of my countries I can now say profit.

Drop it in there.

And there we go.

We now got the sum of the profit by each of the countries as well.

So there we go.

That's an introductory lesson to creating calculated columns.

We're going to continue in the next lesson.

I'm going to look at some dates.

I will see you in that lesson.