- 8 months ago
Ready to take your Power BI skills to the next level? This intermediate-level Power BI tutorial goes beyond the basics and dives into advanced techniques for visualizing and analyzing data. You’ll learn how to *customize stacked column charts, funnel charts, treemaps, and matrix tables; apply *DAX calculations; and optimize reports for *better interactivity and insights.
https://1stepgrow.com/advanced-data-analytics-using-python/
https://1stepgrow.com/advanced-data-analytics-using-python/
Category
📚
LearningTranscript
00:00Let's try to solve these five questions in the class. And after uploading the data,
00:12the first thing I will do is check the data type. Okay, so it looks quite fine. Now let
00:18me read the question. Create a visual to display number of employees on the basis of seniority
00:26level. So like tenure, if more than five years, seniority level should be senior. If it is between
00:33three to five years, junior one to three intern, less than one tenth. So first of all, in my data
00:43set, I have the date of joining of employees. So using this date of joining, I need to calculate
00:50the tenure means that how many years he has completed in the organization. So how do you
00:56calculate tenure generally from date of joining? If I subtract today's date, as of today, what is
01:03the tenure I'll be able to get, right? So let's try to do that. So first of all, yeah, let me go to home.
01:11I'll click on transform data. And I have this column called as date of joining.
01:18So I would like to calculate tenure in years. So I'll click on add column. And since this is a
01:24date operation. So I'll look for some features in my date icon, I'll click on date. And do you see
01:32this option called as age? What does age does? So you can read about it, create a new column that
01:40contains the duration between the current local time and the values in the selected column under date.
01:49And the option is called as age. And I click on this option age.
01:56And a new column has been cadded called as age. And this is in I think in days. And I want to
02:06convert that into years. I don't need to write any formula. I'll click on duration.
02:10And I will select total years. So now I got a new column called as total years. So in a two step
02:20manner, I will be able to calculate. And this is nothing. I think let me do some random check. The
02:27first answer is 10.8. And the date of joining is 2012. Approximately. So that's correct. So approximately
02:3610 years. Yeah. So let me rename this column. As 10 years.
02:48Okay, and now I'll go to home. Click on close and apply. Yeah. So any questions? Anything you want me
02:55to repeat? Yeah, just what you did actually I was on my power BI window on that side. I didn't
03:03saw. Sure, sure, sure. So let me do that. There are two steps. Let me delete this column. And let me do
03:10that. Yeah, because it's actually there are two steps which you need to take here. So you go to home.
03:19Click on transform data. Yeah, let me do that. And let me know when you when you reach here. And once you
03:29reach here, click on the column, date of joining. So when you click on a column, which has the date data
03:39type. So all the operations related to the date, they will get highlighted, they will become active.
03:49Okay, so when you click on a text column, the options related to a text column will get highlighted.
03:57So I click on the date of joining column. And since I don't want
04:03this column to get impacted or to get modified. I would like to click add a new column. So I'll click on
04:12add column. If you don't click on add column, it will transform the existing column. I don't want to
04:20transform the existing column. So add column.
04:25And then on the ribbon, you will see this option called as date.
04:34Click on date drop down.
04:39And then click on this option called as age.
04:41So if you are using this with date of joining, it will calculate the age as per today's date.
04:52So what it will give me the tenure. But if I use this option along with the date of birth,
05:01it will give me the age as of today of their employees.
05:04So I click on date of joining and click on this option date. And click on this option age.
05:14First step. And let me delete the step which I have already created. Let me delete this option.
05:19So as I can see, uh, in my system, the date of birth and date of joining has different format.
05:29Like your format is like date month and year. And my format is like month date and year.
05:37So where can I go and edit it? Okay. Okay. So no problem. So what you can do, uh, let me come out of
05:44this option. Okay. So let's do one thing. Let me discard. So go to file, click on options and settings,
05:55click on options and go to, uh, regional settings. And the locate, which I'm using is English India.
06:06Can you check whether you are also using English India? Yeah. Okay. So I got it. You told this.
06:13Yeah. Yeah. Yeah. Just check. So we should be on the same locale when we are working together.
06:20It might be us. That's the. Yeah. Yeah. Yeah. Yeah. Kindly check.
06:25Was it English us? Oh, okay. Okay. Yeah. So change it to English India. Yeah. So when we are working
06:32together, we, uh, we should be on the same locale. Okay. So for the purpose of this course. Okay. So,
06:39uh, now once again, let's try to transform this column and calculate, uh, let me delete this column
06:47also page. Yeah. Once again, let's do it together. Go to transform data, click on date of joining
06:58column, click on add column.
07:03And you will see this option date, uh, as the icon and click on this option age.
07:12So you will get a new column called as age. I want the answer to be in ears.
07:20Once again, you will see an option called as duration on the ribbon.
07:25Click on the option duration. And select total years.
07:35And now I got a column called as total years. And this is nothing. This is my tenure
07:40in years. So I'll rename this as tenure.
07:45I'll go to home close and apply.
07:58I, uh, is everybody able to follow this instructions?
08:04Yeah. Yes. Okay. Perfect. Perfect.
08:07Okay. So yeah. Now the problem is there are too many decimal positions. So I would like to fix these
08:13decimals up to two places only. So I'll click on my tenure column and type, you can see the, the word
08:22is auto here. So click on this place and just type two. Enter. So the decimals will be up to two places now.
08:33And that is how you can fix your decimals.
08:35As per your choice. Okay. So now I have the tenure up to two decimal places.
08:43Now what I need to do, I need to categorize them as per this condition. So this is nothing conditional
08:50column. So let's create a conditional column for seniority level. And we will be using the tenure column
08:57for deciding that once again, I'll click on home and click on transform data, add column because I don't
09:06want to modify my existing data. And I'll click on this option now called as conditional column.
09:13And now let me give a new name to this column, which is seniority.
09:20And now I can be look at my condition if the tenure is more than five senior. Okay. So let me use
09:29if the tenure is greater than five, the output should be senior. Add clause if the tenure is greater than
09:40three. Okay, so then it will be junior. Add clause if the tenure is greater than one in turn.
10:01And I think that only one condition is remaining. Anything else is 10 right less than one. So what I
10:07will do, I'll type in the else 10. Okay, so click, okay, and go to home, close and apply.
10:18Okay, now I have categorized my employees on the basis of junior, I have only two answers junior and
10:25senior. Okay, so I have the column now available with me. And in the meantime, now let me try to
10:33visualize to create a visual to display number of employees on the basis of seniority level.
10:41So what is the my dimension is seniority level. I'll go to my report view.
10:48Click on stack column chart and put the seniority level into x axis.
10:55And I need to find count of the number of employees. So I will use amp ID into y axis. And this is my
11:07output. Okay, so I'm not doing the formatting because we all have learned that. And that is how
11:15you can solve this question. The first question. Okay, second question grade the employees on the basis
11:22of salary. A if it is more than 300,000. B if it is more than 100,000. C if it is more than I think
11:30some mistake is there. We have five zeros. One, two, three, four, five. Oh, okay. So maybe we can just
11:37reduce one zero. Yeah, if it is more than 50,000, it is C. And less than 50,000, it should be an A. Okay,
11:46first of all, we have to create a conditional column to categorize these salaries. And where do I have
11:52the salary, I have the salary in another table called that amp cell. Okay, in this, I have the
11:58salary of employees. Let me connect to this data set, get data, Excel workbook and amp cells data set
12:07and click on load. Okay, so I have one more table called a salary. Okay, so this is my salary table.
12:16And this is my salary column. And same thing I will do here, I will once again create a conditional
12:23column to categorize these salaries home, transform data, add column, conditional column.
12:32And let me give a name to this new column as great.
12:39Okay, so this is great.
12:40Okay, so column name if I did not select the second table. So second table, after selecting the second
12:51table, then only click on conditional. And this one is great. If salary is greater than or equals to,
13:00okay, it's only greater.
13:01So it's the same data set with the name employee salary, right?
13:09Correct. Correct. Absolutely. Correct. Absolutely. Correct. Absolutely. Okay.
13:14And output should be.
13:17As.
13:21A.
13:23Pad clause.
13:28Salary is greater than
13:31100,000.
13:36The output should be B.
13:40And C if it is more than 50,000.
13:53Okay, if it is more than 50,000, the output is C.
13:56Otherwise, answer should be an A. Okay, everybody. So one, the one mistake was there.
14:07The third one is 50,000, not 500,000. Third condition. Okay, so now.
14:12Click okay, go to home, close and apply. So I have the grades now. Now let's try to visualize,
14:23create a visual to get the percentage wise bifurcation of the grade. My question to all of you is,
14:30which visual by default calculates the percentage.
14:38And the second one, and the second one is donut. Absolutely correct. So I do, I will use pie chart or a
14:43donut chart to show the percentage wise bifurcation. So I'll go here, click on pie chart.
14:50In the legend, I will use from the salary table, the grade into the legend and employee ID
15:04as count of employee ID in the values. And here is my answer. So this is how you can create the
15:10solve the second question, which is create a visual to get the percentage wise bifurcation of the grade.
15:15Okay, so
15:19Let's see the third question, create a visual to display the promotion percentage. How will I find
15:26whether the employee has been promoted or not promoted? So maybe I can use some logic here,
15:31I have this table and the designation as on Jan 20th and the current designation. So what I will do,
15:38I will compare designation as on Jan 20th and the current designation. And if both the designations
15:46are same, it means that employee has not been promoted. And if the designations are different,
15:52the employee has been promoted. So I will compare this column with this column and check for equality.
16:01So once again, let me go back, click on home, click on transform data, select my salary table,
16:10add column, conditional column, give a name to this column as promotion, question mark. If
16:20if designation as on Jan 20th is equal to I'm selecting the second column as current designation.
16:33So you can do comparison also using conditional columns. Output should be no means that not promoted.
16:42And if it is not equal, yes. Okay, so no and yes. And I click okay. Go to home, close and apply.
16:59So now I have a new column called as promotion question mark. And I have the two answers yes or no
17:08in this column. And now let me try to read the question. Create a visual to display the promotion
17:16percentage. So once again, I can use my pie chart or a donut chart. To show the promotion percentage,
17:26the motion can be also a case. Yeah, the motion can also be there. But I think let's go with some
17:33assumption that these are only promotions, right? So yeah. And I think that rarely happens. That demotion
17:43thing. So yeah, maybe we can take some assumption that all these are these have been promoted.
17:50Otherwise, you will have to do some more. You have to write some more
17:56DAX calculations, DAX formulas to take care of that situation. Okay, so yeah, now let's try to go back.
18:03And see, I'll click on a donut chart. And I will put my promotion into legend column.
18:14And count of ID into value. So you can see that around 39% have been promoted.
18:25Okay, and this is how you can create a donut chart to show the percentage.
18:29Okay, so this is the third question. Let's go to the fourth question using a funnel chart display city
18:39wise bifurcation of the salary. So three things I need cities and I need salary. Yeah, the first thing
18:46is you should always try to see what you have right now. I have the salary. Okay, and now city, I don't
18:55have the city and city is in I think some other table. Okay, so
19:01So yeah, what do you have to do? We have address in the amp table from the address.
19:10I need to extract the city because there is no separate column for city and both these tables. So
19:17my address is in my amp table, whereas the salary is in my second table. So what I need to do either
19:29I need to create a relationship or I can need to do merging to bring that information.
19:34So first of all, since both two columns are in two different table. But now since we have learned the
19:44data model, what we can do, we can connect these two tables together. So let me go to the data model.
19:50And you can see that automatically Power BI has created a relationship
19:58between amp and salary table. And let me validate that. And you can see that on the basis of amp ID,
20:06a relationship has been created, which is one to one. And the cross filter direction is both.
20:13So Power BI is smart enough and it has a good algorithm. So it will always
20:18be able to detect relationship, correct relationship as well. And in this case,
20:24it has already created that. So now since it has already created, so let me first of all,
20:29work on getting the required data. From the amp table, from the address, I need to extract the city.
20:39How will I extract? So I'll go to home, click on transform data. And what I will do, let me use
20:46something. Let's use column from examples. Okay, so let me use column from examples. Okay, so from the selection.
21:00And let me type a good gown. Okay, so it is able to find a good gown, Delhi, there are doing Noida.
21:09Okay, so all those things. And now only your last record, it is not able to find.
21:18Let me type manually. Okay, so yeah, so good gown. Okay, so now I have Chandigarh, Mumbai.
21:27Just above Chandigarh is also there, right? Anywhere? Yeah, so Chandigarh is also there.
21:38Yeah, Chandigarh is there also. And Mumbai is also there. So it correctly identified it. Only one
21:45mistake it did the last one, which I just manually typed. So I click on okay.
21:50Okay. And I go to home close and let me rename this as city.
22:01And close and apply. Okay, and now let me read my question.
22:07Create use a funnel chart.
22:11So I'll go to my report view.
22:12And click on a funnel chart. And I will put my city into category city wise bifurcation of the salary.
22:26And I will put my salary into values. Okay, so this is how the bifurcation for maximum salaries are being paid.
22:37Or maximum telly.
22:39Purgaon, Deiradoon and like this. So we can show the funnel chart is a good visual to show categories in a decreasing order.
22:48Okay, so this is how you can solve this. The last question.
22:52Create a visual to show or to display city wise count of employees bifurcated on the basis of designation.
23:00Whenever you mean bifurcated. So it means that I have three things here.
23:05I have city then count of employees.
23:09And then designation. So whenever you have three things, so you have to use the legend.
23:14So let's try to do that. Let me add a new page.
23:17And I'll just use a stacked column chart.
23:21City into x axis.
23:24And then count of employees.
23:26Yeah, a ID.
23:28Okay, and I need to bifurcated further
23:32on the basis of designation.
23:34And I will take designation current designation into the list.
23:38And let me switch on the data labels.
23:41And that is how you can see designation spice.
23:43Yeah, so this is the one and legend.
23:46Let me see, I can put my legend on the right hand side.
23:50Top right. Not this one.
23:52Okay, so center right.
23:55Yeah, this is much better.
23:56Okay, so this is how we can show this visual.
23:59We can visualize this.
24:00Okay, so what we have done?
24:01We have actually solved these questions as well.
24:03So along with data transformation, you have to visualize.
24:08You can show using a bar chart also stack bar chart.
24:12That is also fine.
24:14Okay, so this is how it looks like.
Be the first to comment