 Hey guys, welcome to SSU Retail Society site and this is continuation of Power BI Projects, so this is part 5 of Power BI Projects, so today we will see about the attendance overview of any organization by their employees. If you haven't subscribed my channel yet, so I would strongly request to you all please subscribe my channel and press the bell icon, so you can get the notification of our newly uploaded videos. And also like this video and do your comments if you like the video. So let's get started with our project. So this dashboard will be done in this video. So let's start with the left top. So here we can see this is the image of employee head count. So you can download from Google and you can use that image for your project. After that you can see the attendance overview. So this is a text box and the background color is grey and the font color is black and text is attendance overview. In the right side we have used a card and this card will be having for current date. After that in the left top we can see total number of employee by gender. Percentage of male employees and the percentage of female employees will be displayed over here. So in my data it is male employees is 215 and the female employees is 93. So the percentage of the main employee is 69.81% and the female employee is 30.1% approximate. After that present and absent by country. So here we can see the present which is displayed in the orange and in the light we can see the absent. So the country is united states then India then France. So here we can see the total number of days present is 4.4K then 3.0K is the absent. Then in India is 0.7K and in India for absent is 0.5K similarly for the France. The value is very less in the France that's why we are seeing 0.00. In the right side top we can see this matrix inside that matrix we are having the department names. So the department name and after that the present and absent days for that department. Here we can expand on that department. So we can see the employees which is working on that department. Now go to on bottom. So here we can see the percentage of present and absent by months. So here we can see the percentage that is 80% present in the month of Jan. And 20% is absent in the month of Jan. In the month of we can see the 39% is present and 61% is absent. Then the month of March 56% is present and 44% is absent. So this is something column chart which is containing the month in the axis and present and absent in the columns. Now in the left bottom we can see the matrix which is containing the days in the columns like 1120, 1220, then 1320 like this. And in the rows we are having the employees and if the employees present on that particular day then that will be 1 and background color for that should be light green and if employees absent for any particular day then that should be 0 and background color will be red. So if we can move on the right side then we will see we will have the data like this. After that here we can see we are having this slicer. So this slicer will help us to filter the data only for the matrix which is available on the bottom. It will not affect any visualization which is present in our dashboard. So if we will change this let's make it for only 7 days. Then here we will see the data for only 7 days. And the 4th and 5th is our weekend that's why we don't have the data for that and rest we are having 1, 2, 3, 6 and 7. So the absent is only 1 day for the amrita and if we move bottom then we will see more. Here you can see the more. So this is something like that. For example if we are having all the data for all the days and we want to filter for any particular employee. So we can also do that and this employee filter will also help to filter the data in the matrix which is on the bottom of this. Here let me filter for hersting jora. So now we can see we are having the data for him. Now let's move to left side and here we can see these are the days and move to right side. So we can see the absent and present only for that employee which will be selected on this drop down. We will apply the filter on the employee and narrow down the days. So this will be created in this video. So I would request you all please watch this video step by step. So you will not miss any step. This video might be longer because this is a big project not a small project. Let's have a quick look of our source. So we have 2 files. First is the master table. So it would have few columns which is containing the information of the employee. So first is the business entity ID that is the employee ID. Then national ID number. Then the name, birth date, monetary status, gender, higher date, department name, email ID then address one city, country and postal code. So we are having this data. After that let's move to attendance table. So in the attendance table we are having only 3 columns. In actual it might have more than 3 columns. But in our project we require to have only 3 columns. The next column might be the in time then next will be the out time. But that is not required to us. So that's why I have excluded that columns and containing only these 3 columns. So first is the employee ID. Second is the date then the status. For example employee 1 date is 1-1-2020. He was present. That's why you can see P. If we move to 1-2020 then we can see absent. So he was absent for that day. That's why we are having the A. So we are having data like this. So how we can load this data and how we can play with that data. We'll see right now. So go to on start button and let's try to open one more Power BI desktop. So we'll try to import that data on that. After that we'll try to create the beautiful dashboard which we have seen. So we need to wait until it will not be opened. So now this is opened. Now we need to get the data. So click on this and select the excel option. So first let me select the master table and click on open. So it will take few seconds. So we need to wait. So here we need to get the data from it by master file. So we need to select that one. And here we can see the two options. First is load and second is transform data. So I'm very happy to go on transform data. So it will open query window. So inside the power query window we'll see and if any modifications are required on this data so we can do that. So we need to wait few seconds. So now here we are. Now we are good with this data. But one thing let's open this. And here we can see the nulls. So nulls are not required to us. So just eliminate it and click on OK. So it will filter the data for the nulls and one step is added in the applied steps. So we are very happy with it. Now we have done it. Now we need to get the data from another file. So we can go on the new source and let me select the excel. And this time we get the data from attendance file. So we need to select the attendance and click on open. So that will also be opened inside the power query window. So we need to get the data from seat 1. So we can select it. Now we can click on OK. Now here we can see the column 4 and column 5. So these two are not required to us. So we can delete that one. So we can remove columns. So these two will be removed. And one step is added in the applied steps. After that we can also rename this seat 1. So let me call this as attendance. Now we need to click on close and apply. So this will be added and load in the power gate desktop. So we need to wait. Now this is loaded. And here we can see we don't have any relationship between the attendance and employee master table. So now go to on the relationship. So inside the relationship view we need to build the relationship between the attendance and employee master table. So here we have employee ID in the attendance and in the employee master table we have business entity ID. So let's drag this employee ID and drop in the employee master table. And we need to wait few seconds. So here the relationship is done and here we can see business entity ID is mapped with the employee ID. Now double click on this and you need to wait few seconds. And here you can see the relationship is done with one to many. Now cancel it and go to on the report view. So we have done the relationship. Now the first thing is we need to get the image in the left top. So first minimize this filter and now go to on the insert. And here you can see the option for image. So click on that image and this is the employee head count. So let me select it and click on open. You can also download from the Google and you can use that one. We can place this at here. Now we need to use a text box. After that we need to drag this text box and place in the top side. Now try to minimize from the bottom and maximize from the right side. After that we need to write the attendance overview. So we can write attendance overview. So this is attendance overview. Let me select this and try to increase the font size. So let me select the 24 and place is in the middle. After that change this style with the area. Now we are ok with it. Now let me select it and go to on the background and let me try to change this background color with the gray. So this is the gray and the transparency should be 50. Let me make it from here. So this is 50. After that we need to create a measure that will contain the today's date. So go to on the employee master table. After that here you can see the option for new measure. So let me click on that. After that we need to wait few seconds. Here we can see this measure. So now let me call this as today and the formula that should be today. So this function will help us to get the today's date. Now let me click on commit. So it will be committed. And here we can see the format. So we can also change this format what we want. So I am very happy to use the DDMMYY. After that we need to use a card. So now let me click on the card. And try to make it smaller and do one thing just drag this today and drop inside the field. After that make it smaller from the left side and put this in the top. And here do one thing go to on the format and just off this category. After that go to on the data level and try to make this smaller as text. So I am very happy with the 255Ks. Now we can make it smaller from the bottom as well and put it over here. After that we can make the background color of this as we have for the text box. So go to on the background and put on the color let me make it with gray transparency should be 50. So now we have done with the header part of the dashboard. Next thing go to our existing one. So here we need to use this visualization that will have total number of employees by gender. So for that we need to use this donor chart. So we can select this one make it smaller from the right side put it on the top and let me bigger from the bottom. So now go to on the employee table and here you can see an option for gender. So let me drag and drop this gender inside the legend. After that you can see the option for the employee ID that is business entity ID in the employee master. So we can drag this and drop inside the values and here we can see the count of business entity ID so we are good with it and here we can see this is our male and this is our female. So we need to do the formatting only for this. So go to on the format and here you can see the data color so in the female we want to use the orange. So we can choose the custom color and here we can use double F double 6 double 0 so this is for orange. After that go to on bottom little bit here we can see option for detail labels and here we can select the all detail labels. So now we can see the male is having 215 and the female is having 93 and the % is 69.81 and the female % is 30.1 so this is very similar as we have. So next thing what we need to do we need to modify the title of this visualization so go to on the format after that here go to on the titles and here let me call this title as total employees now it should be double E let me try to scroll down the font color let me put it as white and the background color that should be dark blue and put it on the middle now try to make it smaller. Next thing we need to add the border for this again scroll down and here go to on the border and here we want to use the radius so it will be on the curly shape so now we can see this in the curly shape. So I guess we are good with it so let's move to next visualization so what was that so here we have the present and absent by countries so now go to our dashboard and here before going to add this visualization we are required to use two columns first for the present and second for the absent so go to on the data and inside the data let me select the attendance table and in the attendance table we need to add one column that will have the data for the present and second column that will have the data for the absent so here we can see the option to add a column so go to on the table tools and here you can see the new column so we can click on this and it will add one more column and here we can specify the DAX formula so what we want to do this is for the present and the condition if the value of this status is p then that will be the present so comma 1 comma 0 so now we can click on commit so if we will have the status p then in this column we will get 1 if status is a then we will get 0 on that case so let me add one more column that will have the data for absent so we can click on new column and here we need to wait and let me call this as absent and again if status is a then comma 1 otherwise 0 let me commit it so it will add one more column that will have the data for absent so this is absent and this is the present now go to on the report view so here let me add column chart so we can click on this and let me put it on the right side of the existing one and here go to on the attendance table and here let me try to drag and drop the present inside the values from the attendance and the absent in the values so now we are ok with it but here we want to display the data by country wise so the country information is available in the employ master table so here we can drag the country and drop inside the axis so it will have the countries in the axis and the data over here so now what we need to do so go to on the formatting here we can see the country is available in the bottom of this axis and the present and absent over here so we don't want to display these two titles so go to on y axis and scroll down scroll down here we can see the option for title so we need to off this and go to on the y axis and in the y axis we will also get the option for title so let me scroll down and here we can see the title so let me off this so now I guess we have added this one after that we can see the present and absent by country the only thing we need to modify this as the background color of this title and the font of this title so we can select the font as white and the background color as dark blue so I guess this is the same one after that we need to add the border as we did before the format and scroll down here we can see the border let me on that one and here let me make it radius as I guess 25 that is good so we are okay with it so here we can see the data that is available like 0 to 5K so what we need to do we need to make it little bit bigger from the bottom that will have the data more accurate for our information so here we can see it is having the data for 0K, 1K, 2K 3K, 4K, 5K so next thing what we need to do we wanna add the data labels on these columns so the format and here we can see the data label we can on that one so now we have the data as we have in our existing one next we need to add a matrix so go to on the our existing one and here we can see the matrix this is our matrix that will have the department name and if we expand it then we will see the information for the employees and in the right side we have present and absent so we need to create this matrix so go to on the dashboard which we are working on and here let me add a matrix now let me try to make it right side of this and try to make it bigger after that here as I told you we need to have the department name and employee names so how we can create the hierarchy for that so we can press the left mouse button and drag this name in the department name it will create the hierarchy so we need to wait few seconds and it will add one more column that will be the hierarchy like this so now we need to drag this and drop inside the rows so here we can see we are having the rows if we expand it then we can see the employees that is working on that department next thing we need to add the present and absent so that is available inside the attendance so here we have two columns first is present and second is absent so let me try to drag this present inside the values after that the absent inside the values now we can see we are having the same data as we seen in our existing so now go to on the format and do the formatting of this so here go to on the style first and let me try to add alternate rows that might be good after that we can also increase the font size of this so how we can do that here go to on the values and let me try to scroll down and here we can see this text so let me try to increase it so that will be increased like this so that is good after that go to on the columns and the rows so in the rows header also increase the size that is text size is term so that is good and if we want to make it bigger then we can also drag it like this and we can put like this and here if we want to make it little bit smaller then we can also do that here if we want to make it bigger then we can do that and let me try to put it here and here let me try to make it bigger totally by gender now I guess we have made a great progress in our project the only thing we need to create the bottom of this visualization what was that so here if we can see we are having right side that is the percentage present and percentage absent by month so how we can do that we need two measures first for the percentage present and second for the percentage absent so how we can create that one so for that go to on the attendance table after that here go to on the table tools and quick measure so click on that and we need to wait few seconds after that select a calculation so we can click on that after that is called down in the numerator we need to use the present and in the denominator we need to use the employee ID that will be the count now we can click on ok so it will add one more that will have the information for the present percentage so we need to wait few seconds that measure will be added so this is our measure so we can click on that measure and here we can see the percentage so we need to rename this as present percentage and we need to multiply it by 100 so we will get the percentage now we can commit it and next we need to add one more for the absent percentage so we can click on the measure for here so go to table tools and click on the new measure and this time I have copied this formula so we will use that one so let me paste that formula instead of present it should be absent we are doing the sum of this present so it should be the absent now we can do the commit so these two will be the calculating of the percentage of present and percentage of the absent so now we can use the column chart so now go to on the column and let me select that one put it on the right side and make it bigger and make it bigger from the top as well and make it bigger this after that let me select this column here as I told you let me include this percentage of present so let me drag this and drop inside the values and this absent percentage in the values now we need to add the months so here we can see the date that is available inside the attendance table so let me drag this and drop inside the axis and here we need to delete this year then we can also delete the quarter month wise so here we can see the month that is Jan so the percentage is 80 is present 20 approximate is absent so here we can see we are having the data that is in decimal places but in case of present it is in non decimal places so go to on the absent percentage let me select that one in the formatting we can use the whole number we need to wait and here we will see the 20% that is good after that 39% and 61% then 56% and 44% so I guess we have made a great progress after that we don't want to include this month as a title in the x axis in the y axis we don't want to see this percentage present and absent so go to on the format and here go to on the x axis and here is called down and is called down here we can see the title let me off this and is called down for the y axis and we will see the title so let me off that one then next thing we need to do the formatting of this so go to on the data colors and here so in the present it should be green so here go to on the custom colors instead of this we need to use 008000 so this is the green after that for the absent it should be red so we can click on this and go to on the custom color here we can see the red so we can select that part so this is red and this is the green that is good after that we can also enable the data level for this so we can click on that we can see the 80% 20% now the next thing is the title so is called down here we can see the title let me open that one and here let me select the background color that should be the dark blue and the font of that that should be white so let me select that one and make it alignment as center so now I guess we are good with it the only thing we need to take care for the matrix that will have the information for the employee with the dates so we will do now so now go to our existing one and here we can see this is our matrix in the rows we are having the employee names in the columns we are having the dates and here we are having the status that will be the present or absent so how we can create that one so go to our the Power BI desktop which currently working on here let me select a matrix and let me try to make it bigger now what we need to do here we need to drag and drop the employee names so we are having the employee name in the master table so drag this and drop inside the rows and here we can see this is good next thing we need to include the dates so this is our date so let me try to drag and drop in the columns so it will have the data like this so click on this arrow and here we don't want the hierarchy for this so let me select the date so it will have the date only so we can see this is not in the correct format as we are expecting so we can select this date and here we can see the formatting option so we can click on that and here we can select the MD wide so it may be good and will not take a lot of space from the column so we need to wait now I guess it's good after that we need to add the present in the values so let me try to drag this present and drop in the values now it will have the values like 1 1 1 and 0 where we are having the option so now we need to do the formatting so go to on the format and here first go to on the style in the style let me select this so this time I am very happy to use the alternate rows so this might be good or you can select any other option so we are having a lot of options we can select any one of them so I guess this is the good the next thing we need to take care for the present absent if we are having the present the background color should be light green if we will have the absent then that will be the red and the font color should be the white in case of red so again here we can see the option for the conditional formatting so let me scroll down and here we can see the conditional formatting now let me scroll down here we can see the background color so we can on this and after that we will wait few seconds click on the advanced control here we can see we will have the option for color scale so I am going to use the rule here some of the percentage that is good and here if the value of that is 1 so that will be the present here I am using the number not the percentage then that will be the light green so we can use like this after that let me add a new rule and this will for the absent so if the value is 0 of that number then we want a background color as red so we can go to the custom color and select the red now click on ok now we need to wait here we can see we are having the data like this but again as I told you the font color that is white then black that is not good for us so we need to change that so here we can see the font color click on that and here let me scroll down little bit and click on the advanced control so in the advanced control we can see the color scale so I am going to use the rules and the first is if the value is 0 then we need to make it as white now we can add a new rule and value of the the present is 1 then we need to specify that as black now we can click on ok so here we can see we are having the 0 and 1s as we are expecting so we are in the good and we can scroll on the right side and we can check the values that is our expectations now the next thing and the last thing we need to add two slices first we will be going to filter the data that is available in the dates second will be narrow down for the employment names so first let me try to make it little bit smaller and this as well and go to on the matrix and try to make it smaller this matrix as well now here go to on the slicer let me select first slicer and try to make it smaller and it will have the data for the start date and end date that will be filtered so here we can go to on the attendance and let me try to drag this date and drop inside the fields so we can see this and let me try to make it little bit bigger so now we can see that after that we can add one more that will help us to filter the data for the employees so now we can add one more slicer and this time make it smaller and put it on the right side of that slicer and the name from the employee master table now go to on the employee master table and try to drag this name and drop inside the field after that go to on the formatting and here go to on the selection control we want to see the select all option over there and here let me click on this arrow and make it drop down and try to make it smaller from the top now we can click on this and do some more formatting like for the border we can on the border of this and we can open it and here let me scroll down and the radius it should be 25 I guess next thing we need to make the background color of this we need to make these two changes so click on the format and here we can see the background so we can click on that go to on the color we can select the gray and scroll down transparency that should be I guess around 50 so we can select form here 50 so now go to on the name format and here we can see the background color click on that scroll down in the colors we can select the gray and transparency that should be 50 so now we are good with it and here let me try to make it bigger I guess this is good if we will scroll then we can see all these values the only thing we need to take care for the title of this name so here go and off this slicer header and try to use the title on this title and let me call this as employee name and here let me try to scroll down and the font color is black so we need to make it as white and the background color is white and we can use as black so we are good now here let me select this one and try to go on the date inputs and here the font color that is good the background color is not very good so we can select and this is the background color the font color should be white for this so now I guess we are good now we can make it bigger from the top make it bigger this as well and we have done this and the only thing if we will make the change on these two slicers then it will affect all the visualization which is available but what our expectation is if we will make the change inside this then it will only affect our matrix which is available on the bottom so click on that go to on the format and here we can see edit interactions so click on that here we can see the none so we can click on that none none and none now select the employee name and here we can click on none none on all these now we can again click on edit interactions now if we will make the any change over here it will not affect any other visualization it will only change the values inside the matrix now we can see it is going to narrow down let me select any employee so let me select the amrata here we can see the data only for amrata and there is no any change for other visualization so let me select all so this is what we did this video thank you guys for watching this video I hope you have understand how you can create this beautiful dashboard in the Power BI and if you need the source it will be available on the google drive I will paste the link of the google drive on the description of this video so if you need then you can download their files and you can do the practice on your own and still you have any doubt then you can comment on the video and I will try to response on that and if you are having any project or any scenario then you can share with me and I will try to record the video on the same thank you so much guys for watching this video and do subscribe my channel press the bell icon and like the video thank you so much guys