Power BI - Creating calculated columns

Hello friends and welcome to Tutorials Point

In this segment of the video we are going to see how to create calculated columns in Power BI Desktop

So what is this calculated column? It is a column, it's a new column that we create using an expression in such a way that

we can combine or transform two or more elements in a dataset

So why we do that, it is a simple way of enriching and enhancing your data

So giving you a simple example about creating a calculated column is that let's take for an example that I have two columns in which the first column have the first name, the second column has the last name

I will create a calculated column in such a way that I will be getting the first name and last name together in one column

And for that we need to build an expression. Now this is the one reason that we create a calculated column for

Another reason of creating calculated column is to create a relationship. between two data set. As you can see out here, a calculated columns helps me to create a relationship or establish a relationship between two tables with no unique field that exists to create a relationship between them. So that is the reason I create a calculated column in such a way a relationship can be built among them. All right? So how we create this calculated column? It's quite easy. Now we have to go under the modeling tab and under the modeling tab. And under the modeling tab

you will find a button which says new column. The moment you click on this new column button

a formula bar will get activated and out there you have to type the DAX expression. So what is this

dash expression? It is very simple. It is a data ysis expression used in Power BI. So when you

start typing into this formula bar, the list of DAX expression will be suggested by PowerBi

the moment you start typing the first letter. So automatically it will give you suggestion that

which expression you have to choose from. Alright, so we'll be learning more about how to create this calculated columns in our practical approach

Hello friends and welcome. In this video we are going to see how to create calculated columns in Power BI Alright so let go ahead and do this Now in order to create a calculated column let me tell you why it is needed the most

Okay, so if you can see on the extreme right side of my screen, I have all the fields

So I will go under the location field, okay, and I will simply drag this city out here

I will get a tabular format. Alright, so I have a list of all the cities and

and I will go into the sales table and I will just track the revenue

Now the moment I drag revenue, do you see there is an error in this table

As you can see, all the values in this tables are signifying just one value, right

And even the total turning out to be the same. So this is the error which you might have to face sometime while creating the reports

So in order to avoid such kind of errors, what we'll be doing will be creating a calculated field

and defining a relationship between them in such a way that whenever I drag this field, I should be getting the relative numbers along with it

All right, guys. So let's go ahead and see how it is done. So I will move under this data view

All right. In this, I can see all the data which we will be using for our Power BI report generation

So let's go ahead and what we'll be doing out here will be adding a new column, a calculated measure, a calculated column, not a calculated measure

We'll be adding a calculated column with the help of which I'll be combining the country column and the zip column. Okay, so let's go ahead and do this. So in order to do that, I need to go to the modeling tab and under modeling tab, I have something called as new column. The moment I click on new column, under the formula bar, you will able to see something. Just wait for it. It's working on it. All right. So now, as you can see, a new column

which is blank as of now because I need to define that column and this definition can be created

in this formula bar. Now out here will be typing a DAX formula that is nothing but a data ysis

expression with the help of which we be creating this column Okay so what I want to do is I want to combine this country and zip altogether So I will simply type country The moment I type country it has already picked up from this location table

Please pick this column. So I'll simply double click on it and it gets highlighted and I'll be using an ampersand

That is my and sign. Okay. And double quotes to just add a comma between them, right

So I've added a comma between them. All right. And again, an ampers and

and I would like to say the zip code so basically it is taking from the table

the tool is such a brilliant tool that it understand that which table I'm

talking about and which column I'm talking about altogether so I'll simply

double-click on it all right now it has been created now the moment I will hit

enter all these name all this data can be seen out here in which I will be

seeing the country name and the zip code along with it also we'll go ahead

and change the column name so I will name this as country zip okay and I'll

simply hit enter the moment I hit enter it is started working on it and out here

you can see I have the country name and the country zip along with it all right

so it was that simple as you can see this column has been created and this FX

shows that it's a calculated column we'll do the same things with sales as well all right

so we'll go ahead and go under the modeling tab I'll say new column all together all right and

same I will type the same tax feature for a function that is country zip all right and I'll say I would like to get the country name

the country name double click also an expression that is the ampersand which helps me to join all right

again an ampersand and I would like to get this zip though so basically it is what it is doing it is taking the sales

From the sales table taking the country name and from the sales table it is taking the zip okay and simply double click and I hit enter as you can see the same thing has happened out here as well

Just give it a moment It takes time the reason being it is taking time as it is

working on all most you can see this one one carot rules which we can see out

here so it's a very huge database so technically it does take time to process all

this thing altogether all right so this is has been happened as you can see I

have the country name and I have this zip along with it so be We have created two new calculated columns

Now we have to define a relationship between them. So in order to create a relationship, you can simply go under managed relationship, but we can also go under this relationship view in which I can see all the columns

We have made changes to the location column and the sales column and we need to define a relationship between them

As you can see under the sales column and under the location, we both have the country zip

So I'll simply select this and drag it with the help of my mouse and drag it to the location

So the moment I leave my left button, what do you say, relationship has been created between these two table in such a way that they are interconnected

As you can see, there is an arrow sign that is like one to many

One to many connection. This type of relationship is defined as one to many

right so this is what the relationship has been created now let's go ahead and see

whether this after making this changes will my table change or not so I will

again go to the report tab and under the report tab I will again take the city all

right I will simply take the city I will drag it here and I will also take the

revenue so that it will give me the proper data set so it is giving me

revenue by the city now it's showing in a job graphical manner so it will give me on a map but let's go and check it in a tabular format

whether it's giving me the right figures this time yes absolutely now as you can see this

has been giving me the right figures and the right total at the bottom so this is how you can

create a calculated column in power bi with the help of the modeling tab all right guys so this was all

for the video see you and till next time