 Hey guys, welcome to SSUnitex, so since this site and this is continuation of Power BI Projects. So today we are going to discuss one more real time project which is related to the HR. So basically in the HR Management dashboard we are going to see the information which is totally based on the employee. So here as you can see in this dashboard so we are having multiple cards and we are going to use multiple visualizations as well. So let's start with the top side. So in the left top you can see the HR Management dashboard which is the simple text and below that you can see the date and time and on the right side you can see the total number of employees. So here we could see we are having 23.49k. So these are the total count of the employees. Then we can see how many male employees we are having and how many female employees. After that we can also check about the full-time and part-time employees as per their genders. Then on the right top you can see the new hire. So here like we are going to use the image for the new hire. So the total you could see is 1992 and the male employees is 1435 and the female is 557. So basically in the top side you could see all these. Then in the left side you could see we are having 4 filters. So the first filter which is related to the full-time and part-time. The second filter which is gender then we are having the department then reason. So we can do the filter accordingly. And after that you could also see we are having the department gender-wise total employees. So here as you can see basically sales and marketing department the total count of female employees is 1373 and the male employees is 5942. So it is sorted accordingly. And after that in the middle top you could see gender-wise rating count. So here you could see we are having line chart. So in this line chart you could see we are having the male and female. So according to the rating we can see. Then below that we are having this funnel chart. So in this funnel chart we could also see like the employees by their experience. So here the first one we could see less than 3 years, less than 5 years, less than 11 years, less than 16 years and more than 16 years. So that we could see over here. Then on the right side you could see award won by department. So how many awards department employees has been won? So that is going to display on this visualization. Then below that again we are going to use a line chart that is for the education-wise employee count. So here we could see bachelor's, master and above. Then we are having the blank and below secondary. So it is indicating according to the education of the employees. So this information is very important as we are going to check about the employee data in the organization. So this dashboard is the first one and we will be going to see more dashboards on the HR. So let's get started with this dashboard and we will see how we can create that. So first of all let me check about the data. So for that our data is in Excel file as you can see. So here in the top as you can see we are having the employee ID, then department, then reason, then education, then gender. Here we could also see the recruitment channel. So that will be the sourcing or others or many more options are also available. Then number of trainings, how many number of trainings that employee has been attended? Then what is the age of that employee? Then we could also see the previous year rating. Then we could see the length of service. So here we can see 1, 2, 3, 4, 5. Then we could also see the KPI meet more than 80%. We could also check how many employees are going to lie in this category. Then how many awards one could also see on the column L. Then average training score and the last column that is the full time and part time. So this is our source data. So by using that data we are going to create that dashboard so that you can see over here. So first of all as you can see we are going to use few images. So you could also download these images from the Google. So here this is for the total employees. So this is total employee headcount. You could search directly on the Google and you can download and use that one. Then this is for the male employee. This is for the female employee and here you could see this clock. So you could also download the image for the clock and accordingly you can use that. Then on the right side we are going to use this new hire image and after that this is like again total number of employees then male and female. So these images you need to download from the Google and after that you can start working on this dashboard. So first of all go to on the new Power BI desktop and this is the blank Power BI desktop. So here in the Power BI desktop we can see the option for the import data from the Excel. So we can directly click on that and after that we need to select the Excel file. So we can select that one and after that we can click on open. So once we click on open then it will open a navigator page. So in the navigator page we need to select which seat we need to load inside the Power BI desktop. So after selecting that one we can directly click on load in this case. If you want to do certain manipulation then you could also go inside the transform data. So now we have successfully loaded the data inside the Power BI desktop. Now we can go inside the data view and we can select that table. So after selecting that table we could see the data which is available on that table. So that you can see employee ID, department, reason everything is available as we have seen. So let's try to create the dashboard. So first of all we need to use a text box. So we can directly click on that and after that we can also resize that text box. So we need to resize that and like this. Now go to on the background. So in the background we need to put the color like little bit gray on this. So it seems good now. Now go to on the existing one. So here as we can see the first thing that is HR management dashboard. So we have written that. So now let me try to complete this and we can directly use that new one. So we can paste that. So it seems good now. The second thing that you could see the date and time. So for date and time we need to create a measure and that will be going to display current date and time. So for that go to in our table and here let me try to create a new measure. So we can directly click on the new measure and here we can call this as current date and time. So for that we could use the now function. So by using this now function we can directly create the current date and time. Now so for displaying the data for this measure we have to use a card. So we can directly click on the card and we can try to resize that. So like this and like that. Now let me try to drag and drop this current date and time. So it seems good but here as you could see it is having little bit problem with the display. So we need to resize this because the font size is very big now. So go to on the format and here go to on the data level and inside the data level the text size that we can decrease by 18. And after that we can also see the font family. So it should be aerial. So this is the standard one. And after that we could see here like category. So we don't want to see the category. So now it looks good. And after that we can little bit resize that and try to put the below of this HR management dashboard. Here again as we can see we don't have any background color for this. That's why it is white. So now go to on the background and inside the background we could select this gray color and here go to on the borders. So inside the border we could use the radius. So we could use the radius up to I guess 20. So now it seems good. So we have done this one. And after that as we can see in our existing one we are having this line. So we have to use this line to make the differentiation between these two blocks. So here go to on the insert and inside the insert we could see this one. So we could click on the line. So here we could see this line. So we can put this line over here like this. And here go to on the line and in the line color we can use the dark gray color. So we can select that one. So now it seems good. And we could see it is going to differentiate between two blocks. Now the second thing that we are having this total employee. So this total employee inside a text box. So again we need to insert a text box. And inside the text box we can say like total employee like this. And here try to make it a little bit smaller like that. And try to put top of that. Here go to on the background color and we can use the background color as gray. And again we can go inside the border and we can use the radius over here. Now it seems good. And after that as we can see in the existing one we are having this image. So this is for the total employee. So let me try to use the image. So here click on the image. So once we click here we can see this is for the total headcount. So we can directly use that. Now try to adjust with the size like that. Put it below that and resize a little bit more. So this is for the total headcount. Now we need to display the value of the total employee. So for that we have to use a card. So we can directly click on the card and try to again resize this card like that. And try to put below of that image. So and after that we can directly drag and drop the employee ID. And here we want count of the employee ID like this. But here as we can see the font size value is very high. So we can go to on the format go to on the data level. And it should be 18 and here font family should be a real of the category. Now it seems good but the background color is not as we are expecting. And go to on the format again go to on the data level. The font size little bit we can also decrease from 18 to I guess 15. And the background color that should be gray. So go to on the background color it should be gray. And in the border we could increase the radius. Now it looks good. So we have done like this one. But in the total employee we could see the background color which is not as the others. So for that go to on the background color again. And here let me try to click on that. Now it seems good. Now the next thing that we need to display the total number of male employees and after that total number of female employees. And we need to also display the percentage of the male and female employees. So for that we need to calculate the percentage of these two. So here we need to create two new measures. So click on the new measure. And this will be the male percentage and next will be the female percentage. So how we can calculate the male percentage. So this is very simple. We can use the calculate function. And inside the calculate function we can directly count the number of employees. So this is the employee and which employee you want to count. So inside the filter. So first of our table then gender. So gender that should be male. So we are going to calculate total number of male employees. We need to divide this with the count of total employees. And we can close the bracket and we can directly put the enter. And here we want to calculate the percentage. So we can directly click on the percentage. So this is the male percentage. Now let me try to copy this code and click on the new measure. So this time we want to calculate the percentage female employee. We can write the female percentage. Now we can put the enter. So here we need to click on the percentage. Because we want to see the value inside the percentage. So that seems good. Now here what next we need to do? We need to copy this card and paste. And try to put this on the top side. Once we put this on the top side. Then we need to drag and drop the male percentage that we have calculated. What we can see it is 70.65 percent. So that seems good. And next we can copy this and paste one more time. And try to put this on the right side of that. And this should be female percentage. So we can directly drag and drop the female percentage over here. So this is the female percentage. So the values are same because we did not modify this male to female. Now put enter over here. Now we can check the value. So here it is 29.35. So that seems good. Now in the existing one we can see we are having two images. So we can directly import those two images. So we can click on image and first for the male. And we can a little bit resize that male image. And try to put this over here. Next for the female we can again click on the image. And select the female and try to resize this and put below of that like this. Now we need to use two more cards. So those cards will be used to display the count. So for that we can directly click on the card over here. And let me try to drag and drop the employee ID over here. And it should be count. So we can directly select the count. Go to the filter and inside the filter what we could do. We could directly drag and drop the gender filter over here. And select the male employees only. So now it is showing how many male employees we are having over here. And let me click over here. Go to the format and drop it here. So the format is same now and that seems good. So we can directly drag and drop it below of that male. And we can also resize over here. So that seems good. Let me try to copy this and paste one more time. Put below of the female. So here as we can see in the gender. Now this time we can select the female and select the male. So now it seems good. So we have done the second block. Inside the third block we could use one more line. So the existing line can be copied over here and paste right there. And this should be placed right here. So now we have done with the second block. Now go to on the third one. So here like we are going to display the full time and part time employees. So for that we have to calculate the full time and part time percentages. So again for calculating we have to create few new measures. So go to on the modeling and click on the new measure. So inside the new measure first we want to calculate the full time male employees and full time female employees. So for that we could see full time male employee and full time female employee. So here again we can use the calculate function. So calculate then we want to do the count of the employee ID and put comma then we need to do the filter. And inside the filter as we can see the table. So here we can select the table that is HR analytics put comma here. First of all we need to do the filter on the gender. So gender that should be equals to male. And the second filter that should be on the full time and part time. So here we can see FT and PT that should be equals to FT. Now we can close the bracket. So this is going to calculate how many number of male employees in the full time. But we want to calculate the percentage. So we can use the divide and again we can use the calculate function. Then we could also use the count of the employee ID and here we want to do the filter and this filter on the HR analytics table where your gender value that should be equals to male. So what it is going to return it is going to return how many percentage of male employees in the full time. And next we can calculate for the part time and similarly we can do for the female employees. So let me try to copy this and put enter. So here we have missed using percentage and we can click on the percentage. So it will calculate the percentage for that. We can put enter and click on the new measure and inside the new measure now we are going to calculate for the part time employee. So go to on the top side instead of the full time. Next that should be part time and here instead of the full time that should be part time. Now we can put enter. Now we can click on the percentage. So it is going to display the percentage of the part time male employees. Click on the new measure and this time we want to calculate the full time of female employees. So this is the full time of female employees and here instead of the M that should be F for the female and here full time is okay. Now we can copy this and put enter and click on the percentage. So that looks good. Now click on the new measure. So this time we are going to calculate the part time of the female employees. So instead of this Ft that should be Pt for our time. Now we can put enter. Go to on the existing one. So here as we can see we are having this clock and after that full time and part time text boxes then the image and after that the percentages. So similarly we can do for those. So go to on the insert and try to get the clock image over here and click on the open. Now we can little bit adjust the size of this clock like that and try to put top side over here. So now looks good. Now we can use two text box. So click on the text box and this is for the full time. And select that try to resize that try to put below of this clock box. So this is for the full time. Let me try to copy this and second we want to calculate for the part time. So now we can copy this and paste it here and put below of that instead of the full time that should be part time like that. Now we could use few more cards to display the values. So here select the first card and here let me try to drag and drop the value for the mail full time. So we can use that one and here if we select it is not selected the percentage. That's why the values which is not as we are expecting. But on the format we don't want to see the category so we can off this category. But on the data levels and if we don't want to use that one we can directly select the existing one and use the format right here. So now it seems good. Now we can resize this and try to put right side of the full time like that. Now we can copy this and paste one more time and this time part time mail employee. So here we should see PT mail so we can drag and drop it here. So it is 36.16% that seems good. Now here we can insert the image for the mail employee first and second we could use the female employee. So this is the mail employee so we can drag and drop and set it here. Like that. Next we could use the female employee. So for the female employee we can also adjust that. Like this try to put right side of this mail. We could copy these two cards and paste it here. Try to put below of this female employee. Now we can drag and drop the value for the part time female employee over here. And here we did not select the percentage that's why we can see the values right this. Now in the female employees we could see the same value. So we need to drag and drop the full time female employee that is 64.48% so that seems good. In the last block we can see it is for the new hire employee. So for that again we can directly copy this and paste this line and try to put right here. Now we can directly use the image for the new hire employee. So this image we could use and click on the open. And now we can resize this and we can put right there. Then we could use one more image for the total employees and we can resize that. And put on the right side of new hire image. Now we can use one more image for the male employees. And next we could use the female employees and put right side of that. Now for the female employees let me try to use that. Now it seems good the only thing that we need to copy a card. And try to put below of the total. Here everything is good go to on the filter and inside the filter it is selected for male. So we can delete that one. Here we could see the length of service. So we can drag and drop the length of service inside the filter. And here in the length of service we could select the basic filtering and select only one. Now let me try to copy this and paste. And put it on the right side and we can also a little bit adjust this. So we can easily set all these. Here we need to select the gender and inside the filter we can select the male. Now let me try to copy this and paste. Put it on the right side of this female. We can adjust this a little bit. So we can easily set all these like this. Here instead of the male we could select the female. So everything is good in the top side. So next we need to add the filters that you can see on the left side like full-time, part-time, then gender, department and region. So we need to add all these filters. So go to on the new one and here let me click on the slicer. So first of all let me try to drag and drop the full-time and part-time value over here. Then go to on the format inside the format go to on the general and inside the general instead of this orientation vertical this should be horizontal. And after that responsive we can off that. So that looks good and after that then go to on the items and inside this item you can see the font color and background. So inside the background we can click and here we can select this one. Now it looks good and after that the font color that should be white. So we can make this as white. Now it looks good now we can little bit adjust this one and like that. And you can put this over here now we can copy this and paste one more time. And this time we can put this below of this and here instead of this full-time and part-time this should be gender. So here we can drag and drop this gender right there. So now we can see like male and female over here. If you want to increase the font size then we can also increase. So go to on the format go to on the items and inside the item we could see the text size. So we can increase this text size up to 18. We can format this and use that format over there. Next we need to add the filter for the reason and different. So let me click on the slicer one more time and drag and drop this department over there. Now it looks good and we can put below of. Now let me copy this and paste one more time try to put this below of that. So this is for the reason. So we can drag and drop the reason over the fields and remove the department. So now it looks good. We can select all and select all. So we can a little bit do the formatting as well because here we cannot see the select all option. So go to on the format go to on the selection control and inside the selection control we can select all so we can so that so we can use that right there. Similarly for the reason we will also select all option so over there. So we have done with the filters. Next as we can see we are having department gender wise total employees. So for that again we can use this cluster column chart. So here we can put the right of this filter. Now so in the axis we want to see the department so we can drag and drop the departments over there and after that we want to see the values so that will be the count of the employee. So employee ID should be inside the values and it should be count instead of the sum. So now it looks good but we want to see this as gender wise so we can drag and drop this gender inside the list so now we can check the values and now it looks good. Here we can do little bit formatting of this as well because here we don't see the data value. So for that go to on the format and here we can see the data levels we can directly on that. We can also modify the color of this so for that we can go on the data color and for the female we can select the pink color and for the male we can select the other color which is light blue I guess this one. So now it looks good if we can select all inside the reason as well as inside the department then we could see the total value for that. So move to the next one you can do the formatting for this header and the title. So next we have gender wise rating count so for that we have to use this line chart and inside that line chart we have the previous year rating so that you could find out from here so we can drag and drop this in the access and inside the values we just want to see the count of the total employee IDs. So here instead of the sum we want to see the count and here as we can see we are having the total values in a single line but we want to see in the male and female so we can drag and drop the gender inside the listen so that we can see we are having two different values right there and if you go here then you could see we are having this dotted line so what is this dotted down so we can select this line chart go to on the analytics part over here that is a trend line so we can add a trend line over here so that should be like that now it looks good we can also enable the data levels so it is going to display the values for the particular data level now we can little bit decrease the size and resize and put it here so now it looks good we can also set the background color and title so you can make that on your own so now next we want to see the award won by department so for that again we need to use the cluster bar chart so we can put a right side of this line chart and little bit adjust with this so here in the access we want to see the department so we can directly drag and drop the department in the access and inside the values we can directly drag and drop the award won in the values and this would be the sum so now it looks good we can also on the data level for that and we can also do the background color instead of from the white we can make it little bit like that so now it looks good next thing as we can see in the existing one we are having this education wise employee count so here we can use the line chart for the same and put it on the right side so as you can see in the access we are having the bachelor's masters so this is the education so here we need to drag and drop this education on the access and after that in the values we can directly drag and drop the employee item and we can also do the count instead of the sum here as you can see the bachelor's and the other values are going to display right here we can also split that into the gender wise so this gender should be drag and drop inside the agent so now we are having two different lines and accordingly we can check the data for the same we can go inside the analytics and here we can add a average line so this average line we could as by using this count of the employee ID so now it looks good the last visualization that is remaining this funnel chart so in the funnel chart as you can see this is as per the experience so less than three years six eleven sixteen and plus sixteen years so here we don't have any column which is containing that information so go to on the data here let me add a new column and on that column we can create the experience as we want to see so that experience could be created by using the DAX formula so here first of all let me use the if so if the length of the service so this is the experience so if the length of the service is less than or equals to two then we want to return less than three put comma again we need to check if this length of the service value is less than or equals to five then we want to return less than six then again put comma if we want to check the length of the service if that value is less than or equals to 10 then we want to return less than 11 years again put comma at the last we want to check if the length of the service is less than or equals to 15 then we want to return less than 16 and else we want to return 16 plus years we can close the all these brackets and this is the experience we could say so for that we can use this column now here you will see like this value is not will be going to return as we are expecting let me use the funnel chart very quickly and inside the group we want to use the experience that column we have created so let me drag and drop the experience in the group and inside the values we want to see the employee count so we can drag and drop the employee count and after that instead of the sum we want to see the count so we can directly use the count over there so here as you can see all these values but here it should be like less than three years less than six years less than 11 then less than 16 then plus 16 so this is not as we are expecting so how we can do that so for that we need to create one more table so here we can click on this create table symbol so after that we need to wait few seconds so here let me call this as id column next we will have the value column id column will be like one first we want to see like less than three two less than six then three less than 11 then four less than 16 then five 16 plus so now everything is good here in this table we could call this as sorting now we can click on load so it will be loaded on this popular desktop so we need to wait few seconds so now we can see that table is loaded successfully now go to on the data and inside the data if you want to see the data on that table that is like that so here we need to do the sorting as per the id so you could go over here and in this sort instead of the value it should be sorted on the id now go to on the relationship view and inside the relationship we need to build the relationship between these two as per the experience and value so we can directly drag and drop and make the relationship accordingly now it looks good now go to on the report view and instead of using this experience we need to use the value column from this sorting table so now it is sorted as per our requirement so now we have done with this project you need to do little bit formatting on this so that you can do on your own and if you need the source data of this so I'll be going to provide a link so by using that link you can get the source data of this and you can also find all these images along with the pbix file of this project thank you so much for watching this video if you like this video please subscribe our channel to get many more videos don't forget to press the bell icon to get the notification of our newly uploaded videos thank you so much see you in the next video