Power BI - Creating calculated columns
895 views
Oct 18, 2024
Power BI - Creating calculated columns Watch more Videos at https://www.tutorialspoint.com/videotutorials/index.htm Lecture By: Mr. Akshay Magre, Tutorials Point India Private Limited
View Video Transcript
0:01
Hello friends and welcome to Tutorials Point
0:04
In this segment of the video we are going to see how to create calculated columns in Power BI Desktop
0:10
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
0:18
we can combine or transform two or more elements in a dataset
0:23
So why we do that, it is a simple way of enriching and enhancing your data
0:29
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
0:41
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
0:49
And for that we need to build an expression. Now this is the one reason that we create a calculated column for
0:55
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
1:29
you will find a button which says new column. The moment you click on this new column button
1:34
a formula bar will get activated and out there you have to type the DAX expression. So what is this
1:40
dash expression? It is very simple. It is a data ysis expression used in Power BI. So when you
1:47
start typing into this formula bar, the list of DAX expression will be suggested by PowerBi
1:53
the moment you start typing the first letter. So automatically it will give you suggestion that
1:58
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
2:08
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
2:23
Okay, so if you can see on the extreme right side of my screen, I have all the fields
2:28
So I will go under the location field, okay, and I will simply drag this city out here
2:33
I will get a tabular format. Alright, so I have a list of all the cities and
2:41
and I will go into the sales table and I will just track the revenue
2:47
Now the moment I drag revenue, do you see there is an error in this table
2:51
As you can see, all the values in this tables are signifying just one value, right
2:56
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
3:05
So in order to avoid such kind of errors, what we'll be doing will be creating a calculated field
3:11
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
3:20
All right, guys. So let's go ahead and see how it is done. So I will move under this data view
3:26
All right. In this, I can see all the data which we will be using for our Power BI report generation
3:33
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
3:41
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
4:11
which is blank as of now because I need to define that column and this definition can be created
4:17
in this formula bar. Now out here will be typing a DAX formula that is nothing but a data ysis
4:25
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
4:40
Please pick this column. So I'll simply double click on it and it gets highlighted and I'll be using an ampersand
4:47
That is my and sign. Okay. And double quotes to just add a comma between them, right
4:54
So I've added a comma between them. All right. And again, an ampers and
4:58
and I would like to say the zip code so basically it is taking from the table
5:04
the tool is such a brilliant tool that it understand that which table I'm
5:08
talking about and which column I'm talking about altogether so I'll simply
5:12
double-click on it all right now it has been created now the moment I will hit
5:17
enter all these name all this data can be seen out here in which I will be
5:23
seeing the country name and the zip code along with it also we'll go ahead
5:27
and change the column name so I will name this as country zip okay and I'll
5:35
simply hit enter the moment I hit enter it is started working on it and out here
5:40
you can see I have the country name and the country zip along with it all right
5:46
so it was that simple as you can see this column has been created and this FX
5:51
shows that it's a calculated column we'll do the same things with sales as well all right
5:57
so we'll go ahead and go under the modeling tab I'll say new column all together all right and
6:03
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
6:16
the country name double click also an expression that is the ampersand which helps me to join all right
6:27
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
6:35
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
6:46
Just give it a moment It takes time the reason being it is taking time as it is
6:57
working on all most you can see this one one carot rules which we can see out
7:03
here so it's a very huge database so technically it does take time to process all
7:08
this thing altogether all right so this is has been happened as you can see I
7:13
have the country name and I have this zip along with it so be We have created two new calculated columns
7:19
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
7:31
We have made changes to the location column and the sales column and we need to define a relationship between them
7:39
As you can see under the sales column and under the location, we both have the country zip
7:44
So I'll simply select this and drag it with the help of my mouse and drag it to the location
7:50
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
8:02
As you can see, there is an arrow sign that is like one to many
8:06
One to many connection. This type of relationship is defined as one to many
8:12
right so this is what the relationship has been created now let's go ahead and see
8:16
whether this after making this changes will my table change or not so I will
8:20
again go to the report tab and under the report tab I will again take the city all
8:27
right I will simply take the city I will drag it here and I will also take the
8:32
revenue so that it will give me the proper data set so it is giving me
8:38
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
8:47
whether it's giving me the right figures this time yes absolutely now as you can see this
8:52
has been giving me the right figures and the right total at the bottom so this is how you can
8:59
create a calculated column in power bi with the help of the modeling tab all right guys so this was all
9:06
for the video see you and till next time
#Business & Productivity Software
#Computers & Electronics
#Education
#Software