Creating Calculated Columns

Ian Littlejohn
A free video tutorial from Ian Littlejohn
Excel Power Tools, Power BI and Google Data Studio Trainer
4.6 instructor rating • 17 courses • 111,096 students

Lecture description

In this lesson we cover how to create calculated columns within the Power BI data model.

Learn more from the full course

Power BI Master Class-Data Models and DAX Formulas 2020

Learn how to create data models, relationships and use DAX formulas in Microsoft Power BI

04:27:46 of on-demand video • Updated February 2021

  • Have a COMPREHENSIVE understanding of creating data models and developing DAX formulas in Microsoft Power BI
  • Enjoy CLEAR and CONCISE step by step instruction from a best selling Udemy Instructor
  • Create calculated FIELDS and MEASURES using DAX in Power BI
  • Perform SOPHISTICATED calculations such as Time Intelligence, Filtered Data and Creating Calculated Tables
  • Create RELATIONSHIPS between tables of data
English [Auto] OK, so in this lesson, we're going to start taking things to the next level, so we've got our current data model that we've downloaded from an Excel file. So as we've seen, we've got the fields, we've got the film names. We can change the format and we can do all of those updates. Now, please note, at any point in time, you can actually make those changes and do those updates. And basically the system will then do what's necessary in the background to make sure everything fits. Now, the first part we're going to start looking at over here is some calculations that we can create. And this is going to be called a calculated column. So one of the big things we're going to get used to empower by is the fact that we have calculated columns, which is going to produce a result per row within a table. And then we have something called measures. And so Measures is going to be our next section that we can be looking at. So measured a little bit different measures are actually calculated within a visualization. So we're going to see the measures are not actually part of a table. What they are is a calculation that's applied at the time of doing the visualization. But we'll get to that in the future lesson. But for now, let's focus on the effect of creating a calculated column. So calculated column is, as I said, is basically we're going to be adding on new columns onto this table over here. So the first thing that we're going to be looking at is that we have a list price over here and we also have an order quantity. Now, if we were to take all this price multiplied by order quantity, well, that would actually give us our sales value. So we're going to do the simple calculation. We're going to create a new column called Sales, not to be able to do that. There's a number of different ways that you can add a new column to your dataset. So you'll see even here column tools. We've got a new column as an option over here. You go back to home, you'll see that we got a new column over here. So you're going to see the ability to create new columns in multiple different places. One of the options I like is you can click on your table over here and you can say, I just want to create a new column within that. So when I click on you column now and it's now going to say it's working on it, it opens up a formula ball. So I'm going to see that this formula all works very much the same as as in Excel. So basically, I'm going to be able to put Zaltrap formulas in here. Now, the first thing to note, though, is this is different to Excel is that we are working with the data model, so we are working with field names. So field names are used throughout the system. So we have to have a field name for what we're working with and then you'll see an equal sum and then the equal time works exactly the same as Excel. This would be then the formula that we're going to put it. So in this case, we're going to create a new field called Sale. And please note, you cannot use an existing name of a field that is in your table. Obviously, you've already got a field name, so you can have it twice. In this case, we're just going to say sales equals as simple as that. And what we going to do is we want to now say we want to take this field and we want to multiply by this field. Now, the easiest way I've been able to do that is actually to press your left square bracket. So when you press your left square bracket over here, you'll see that a list of all your field names that are currently in the system will appear. So I actually want to now go to this list price. I just go along, see that that is the field of their double click on it and it's now selected that. Now what I can do is then I say I want to do a multiplication. Now a lot of people go, does the multiplication need to be straight after the closing of the bracket or can you have a space? And then you can do either or if you want have spaces. So it's easier to see your formula. The new spaces markets are like using spaces. If you don't like spaces and you want to put the estrus on right on top of the. That's also fun either. Always. So in this case, we're going to have a space and we're going to have our multiplication sign. So we've got a multiplication sign and now we want to choose order quantity. So that's left square bracket again. So we press the square bracket. And in this case, if I wanted to find all quantity accurately topping it so I could go out and you'll see that it will do a search for the field. And this is particularly useful, obviously, when you're working with large datasets and you got lots of field. And again, now I'm just going to double click on that and I'm going to enter. I guess a good news will see now that we've actually created a new field now in our dataset and we've got it called sales. So you see over here now that over here we've also got the field sales, but it's got a different icon. Now, this is show me that. It's a calculated column. So this is derived within the dataset. It doesn't come in as a native field from your actual source data that you're working with, but it's fine. You're going to find the power by so quick with calculations and things like that for normal data sets and not a not massive datasets. You're not going to see any difference between the fact of fields that have been brought in from the native dataset versus calculated columns. Okay, so it's now part of this. So what that means is if I go back to my report every year and let's say, for example, I wanted to see Obama different countries, I remember we've created our country and we said there's a geographical data data. So what it does by default now is it actually creates this as a map visualization, because we've said that it's a geographical and this Casati done wanted to show the the map. I actually wanted to show the effect of a table. And then we go and we've now got a table. So we're using table visualization. And now if I drag Marseille's into that visualisation, it will then calculate myself just like it would if it was working with any other field within your dataset to calculate it. Columns are really good at that. Now, let's look to the next part of here is the fact that we've actually got a unit price and we've got also order quantity. Let's say, for example, you're putting together your report and you got your sales and you're sending out well, I wonder what my cost is. Now, what you can do is you can actually create your new column from within your report. So when you're working on your report over here, you can actually go to this and you could say you could go to your modeling tab and you can say, I want to create a new column from here, or you could go next to your table over here and you could say, I want to create a new column from here. I don't really matter where you choose. Basically, what you're going to see is the same result. It's going to come up now with our formula and say, OK, you want to create a new column. What do you want to do in this case? We're going to create a new field. I want to call it cost. I want to say equals. Now, in this case, we're going to use the left square bracket to show of list of fields. And and that's my unit price. And I type in u n and I'll see then that the unit price field double click on that. And again, this is a multiplication and we're going to multiply it by the order quantity you see over here. I use no spaces. And as I said, it really doesn't matter if you use spaces or don't use spaces. So we've got a cost equals a unit price times or the quantity press enter on that now and accepts it and I will work on it. And you'll see now I've got a new field, new calculated column called Cost. Drag that into my visualization, all looking good and can what you'll see over here is go back to my data now is I now have a new calculation. So for each row now it is now calculating what the unit prices multiplied by the order quantity to give me a result per row that is in my dataset and my visualization. Now what I'm doing is taking all those results and I'm now summarizing this by different countries to get my results for this for each country and what I'm doing my report, you'll see that I've got a decimal over here. Now, that's pretty hard to see this long list of numbers plus decimals. And I don't really need decimals when I'm looking at millions. So what I want to do now is actually take these two fields and I might want to do some formatting of the data. So if I click the the sales field over here, we'll see the McCollom Talt will now open and we got the name of the field. We've got the data top, but also we got the formatting. I remember I could use my thousand separate of yes, I'm going to click on that. And that's going to turn this into a decimal number format format. I'm going to say actually one zero decimals to Bishan. Now, as I said, this does not affect the calculation. The calculation will still use all your decimals to the right level of precision. But now you'll see that Marseille's field is much easier to see because it's not presenting. The decimal numbers can do the same form a cost over here. And we can go into that again, use my thousand separator and you could say then. No decimal places. And this will be the default, so whenever you use this field now, it will default to not having any decimals. So if you now created a new. Visualisations said, We had our business segment over here and you now put yourselves filled in there, you'll see that there is no decimal numbers, that is now the new new formatting option that will be used. OK, let's just delete that so we remove that one. Now, for our last calculation over here is we got ourselves we've got a cost is we want to profit. So we want to know what is our sales list, our costs to be able to get the profit figure. So, again, I could create it from within my report or I could go back to my data over here. Doesn't really matter, say, new column over here. And now what we'll see is that if I say profit equals when I press the left square bracket. It now shows sales and cost as part of my list of fields. So these are now being incorporated into the data model as new fields within your table. So even though the calculated and the virtual, they are still considered to be part of the table. So I can actually say I want to take my sales figure and I want to minus my cost. So I'm not taking my sales minus my cost figure and I'm going to say equals to net. And then I should get a new field now again, profit. And you'll see line by line, RoboRoach. It's now done the calculation of what Marseille's minus might cost it. And if we go down and our number of rows over here, you'll see obviously there's different values and different figures. This is obviously training data such as demo data. And then we now got a profit. And again, I could go to here, I could go to my column tools and I could change the formatting of this so I could say, again, I'm going to use my thousand separator, which is my comma, and I'm going to say no decimal places. And then we should see the response over here. A decimal places back to my report, drop my profit in there. And by country now I'm seeing my profit. No decimal places been. OK, so that's a quick introduction to the fact of using some calculated columns in terms of enriching our data model with new calculations. We're going to move on to some new examples in the next lesson. I'll see you there.