End to End Data Analysis Project | HR Analytics Power BI Project 🚀

are you looking for a real life data analytics project that you can put on your resume or maybe you are from a different domain such as HR sales and marketing and you want to move to data analyst career well good news with this video I am starting a series of data analytics project where we will take a real life use case on a real data set we are not going to use any toy data sets such as Titanic or Iris flower in these projects we will be using a real life data set and we will Implement dashboards in power bi Hammond very well who is a data analytics manager with many years of experience in power bi will be helping me in this project series we will build amazing dashboards we will share you how a data analyst things and these projects will help you learn data analytics and power bi plus it will build a solid resume portfolio for you in this particular video we have invited finale mandalia from atlict Technologies which is a software and data solution company I happen to be one of the co-founders of this company and she will share employee presence data now as you can see in the screen here we have data from three months and in the columns we have the dates and in the rows we have employee names now as you can see the employee names are randomized so these are fake employee names but the data is real okay so we have randomized the employer IDs and employee names but the data is it's a real data set you can also see attendance key codes so there are course like PL means paid leave SL means sick leave and these chords are present in the cells in in the Excel file and finale generally consumes this file uh just to get answers on her HR related queries we are going to help her build a data analytics dashboard in power bi so in the section that follows what you will see is finale me and Hamilton will be on the call finale will present her requirements Hamilton will build the dashboard I will also be present in the call as an athlete founder and both me and finale are kind of stakeholders or clients for Hamilton and Hamilton will build this dashboard in real time check video description we are going to provide all the resources including Excel file pbix file everything to you so make sure you check the video description let's get started foreign we are using the Excel sheet provided by HR manager pinali and trying to understand their requirements as a data analyst it is critical for you to listen more than you talk in this particular stage of the project this is the stage where you also keep the client completely engaged by asking them the right questions also this is your opportunity to become their strategic partner rather than one of solution provider ask questions to understand their short and long-term plans and align your Solutions accordingly now you might be wondering where do I learn this skill of asking right questions don't worry we are going to cover all of that in today's video [Music] [Music] thank you yeah what I understand from the data is that you have just the current fiscal year like your your fiscal year which is starting from April May to March yeah okay okay so you have the three months of data and you want to combine this three ways of data together to see the insights which you said like you want to understand uh so can can you I saw your email can you please kind of explain me what insights you would like to see from this data sure sure so first of all I want to understand the working preference of people uh you know between uh from home and from office okay yeah so if they are taking a frequent work from home uh either on Monday or the Friday then what could be the reason behind that okay so so basically you have uh you have this attendance key so in that work from home is denoted as okay okay oh and we also have half word from them which means like people work from home for half a day okay yes so we need to consider this and uh yeah okay Sam and you and you said you want to understand the presence of people like how much how much percentage of people are present on a given week or a given month basically attendance right like I didn't understand okay yes and uh because sometimes it happens that if people uh have you know joined the office but suddenly they are not feeling well and they are leaving and they said that I'll be working from home real estate yeah we have to figure out this thing okay so also I understand as an owner of athletic I can I also look at these insights and I have a frequent conversation with finale on this let's say uh if people are working from home if their preference is Monday or Friday it could be either the towards the end or beginning of the week or it could be in the middle of the week so if we get some insights from that then maybe when we are planning some team building activity or team lunch we will do it on a day where majority of the people are presence in the company right the other benefit of knowing this is let's say if you go in a hybrid model where people work from home on two days then they are available on three days then we can do better capacity planning we can have few people coming in in the office and they we give laptop to everyone at Lake so we don't need to have so much rental space we can do better utilizations of the space and save our cost on infrastructure okay that that makes sense yeah all right and uh do you also want [Music] any other things here like for example Maybe yeah um like uh if people are taking sick leave that we we want to understand the reason because uh it might be an indication of covert then we have to take some precautions right so yes so sorry I was just taking notes yeah it's going no that's fine so it can be better if we can get the insights like the reason they are taking cycling so we can take better plan okay so you want to you want to understand on a given day if if let's say 15 of your employees or 10 of employees are taking sick leave which is a clear indication that you know like something is not good because yeah it cannot be a coincidence if too much too many people basically on the same day exactly yeah okay and code is just a special situation but we have seen often there are seasonal afflues there are Chikungunya like little pandemic little epidemics where like many people in the town are uh infected because of like Dengue you know and if we can find the patterns we can enable better sanitization or take special measures I mean we generally have good sanitization infrastructure in the company but we can take spatial precautions spatial measures I have seen here in U.S uh in companies they have doctors coming in they give a flu shots before fall season so maybe we can do those kind of activities if we have good insights on number of people being sick on on given period of the time in a year okay great yeah maybe the other reason is there is a cricket match or something like that you never know if you can yes if you can include that analytics link it with cricket cricket schedule that will be pretty interesting analytics to watch I know I know we have people who I think yes uh [Music] although my first question is like ideally uh finale this should be yeah you know this should be available in the system right because Excel is okay we can do it in Excel I can show you how to do it in Excel but I know that this is what you explained in the email you told that you are you are planning to migrate to a system a new system yes yes when that's gonna happen um I think by next month okay uh all right so what the reason why I'm asking that question is that I can kind of create the the database in a very similar pattern uh yeah new system so you don't have to make a lot of changes because my intention is like whatever we are going to do now you can do it on your own exactly I want to learn the stuff so I can do it my own I don't need to ask you every time yeah yeah right yeah you can I know this is this can become very powerful tool for you once you learn how to control not not at a very advanced level that you can develop uh you know gradually but I think with with sessions like two or three sessions like this and if you take some time to practice on your own I'm pretty sure that you will start doing things on your own this is something I have seen with people you know that I work with you know my friends once they learn the basics and they practice they they become more powerful in their work like uh yeah I've known some Warehouse managers uh it's our people as well you know they start doing stuff on their own so great uh so uh I'm gonna open power bi now and let's see how we can uh import this data and uh so initially we're going to clean the data and understand you know like how we can shape the data for the insights that you want so and whatever I'm gonna do it's it's I'm Gonna Keep the insights that you want in the mind and I'm going to build the database accordingly so this is a database now which is even though it's coming from it so this is going to be in database all right so that's you'll start doing that in this section you will learn how to gather and transform data using power query power query is a feature within power bi that helps you collect and transform data we have our data in Excel and multiple sheets with different column headers and it's a special case that you will see how ham the data analyst Googles the solution you will also see the kind of challenges a data analyst May face in real time and how they approach those problems are you excited download the Excel file provided in the description to practice along you will also need to install a desktop version of power bi from Microsoft store which is free have fun learning so in order to you know let's start with importing Excel work and uh and I will also interview ID on how to update Excel you don't have to refresh the power B on a daily basis we can talk about it at the end of the you know the end of the session I just clicked open data now before going further I want to understand that how I can combine Excel data together see I have it's uh if I combine this Excel data together so what will happen is so all this uh all these Excel files uh you know will add next to each other but have you noticed there is something really uh we need to care about if if I add this data so what will happen is like the April data will be in the top and below the April data you will have the main data right okay right and Below may you will have the zoom data so that's the format we need but the the funny thing is that if we do like that so all your data all your April data will still have the the date as there all your made data will still have the date as equal because if you're adding below the headers are still going to be the same right right yeah it's like just copying this data and putting it here like if I put this data here yes you will have the main data but it will still say that your column will still say as first of it same thing goes with you so that is an easy way to add Excel files together but that works if all your sheets have the same column names so I want you know I want to do something I want to think about a different uh solution which I honestly don't know I need to I need to Google it I need to Google right a solution which will help me combine the data from let's say multiple sheets that has different column names so let's I mean let's let's kind of do a quick Googling together converting data from different sheets in power bi okay so I'm just going to type this okay uh normally what I do is like I open the first I open the first result to see how it looks so this helps me okay okay as I understand from this document you can see that it is it is talking about you know like having same column names this is not going to help us so uh I'm going back to my results okay so let me check this one comparing data from multiple worksheets in the same Excel and let's see what this has for us okay I think this is good right see in the first sheet they have January February March and here [Music] this is exactly what we need we we here they have months and we have what we need is days so which is exactly the same very similar so we can go with this solution so what I'm going to do I'm going to keep this on my left screen I have another screen in my uh in my computer so I'm gonna just call just gonna you know look at the look at the stuff I will explain you these steps and I will also send the link to this uh to this block which I'm gonna use to create and create this power query uh so you can write out yourself so but right now I'll do it and show you how what I'm doing [Music] okay so basically they're saying like don't select any data and simply load the data for transformation okay when you right click here and you say transform data you get that option okay yeah so when I click transform data do you see a new window that opened up right now yes we see that so this is this is called Power query editor so this is like uh data engineering side of power bi where you can transform data clean the data and it's a data preparation uh feature in power bi so let's say that you have like um let's say you have like a you have like a set of employees which are not the part of Catholic that you want to consider but they are still in Excel file you want to you know take out this employees and analyze it freely you can do that let's say you have entered uh by mistake you have entered something wrong in Excel file and you want to change that here you can change it there could be some reason why you could not change your index file because that is for display some people are watching it are looking at that but you want to change in the power query you can change that so all these things you can you can do in public so any questions you have on until now export import the data so far no no no it's clear it's clear yeah right so you know uh just to give you a quick idea so since you have exported this data so just click then you just click on the table you see you know you get the same data what you have in Excel exactly right and uh so you click the may you see the May data you see the dates right these are May dates you click that and Ski and we get all of that all right so for my consideration for my purpose I think uh yeah so let me sorry I forgot I need to read the blog okay uh okay so they have okay they have the list of sheets foreign okay so what they are asking us to do now is to kind of uh create a template so my my ideas see here we have the data correct like I'm taking one sheet here so we have the January data I don't think this is in the right format because the you know the column names are not correct so we need the column names you know One Step Above one row above and uh I also need to see all the dates in one column So currently you see every date is in one separate column but I want all of them in one column so I can it becomes easier for my you know data appending I want to append all the data so if it is all in one column I will have one date column in that one date column I will have all the dates right that will be rather easier right then uh so what I'm trying to say Let me let me open an Excel file and explain so let's see uh so if I have like uh you know can you increase the font size yes sure control scroll is better yes yeah I have April 1 2022. so this is how your April data looks correct right okay and then you are made data looks something like that so you have uh okay so if I just add them it won't work that's that's the reason why we're doing uh you know while you're combining the data in a different way which is going to look like this so I will have instead of having this in each column I will have one date column which is going to contain all the dates like this okay in just one column yeah like one column here okay right and the same goes here so and then I will add this data to two below this one okay so that's the idea that's what we are going to do okay so we'll keep uh all the date in this one column April May and yes all right so according to the blog I need to duplicate this query and create a template okay because the ideas I will not do the transformation in all the pages let's say that next month you're going to add a new sheet right which is going to be April so I cannot I cannot make the transformation for that new ship so whatever transformation I make now that should be also applicable for the new sheet so when you add July August September it should it should work that's the purpose that is the reason why you're creating a template so we are creating a transformation in a template and we are saying for all the sheets apply the same transformations okay it's like say that I'm I'm making one change in one particular sheet and I'm instructing power bi please copy it across all the sheets so it makes your job easier Whenever there is a new sheet it copies the same formatting formatting data transformation data cleaning and all the things okay and uh I think you will agree we don't need this data here because it's it's for uh you know it's for reference purpose but we don't yeah yeah yes and so I'm taking the doubt and uh so we need to keep only the you know this particular uh data table and we remove all of them because this is the this is the table this is the particular column in which we have all the data okay so spinal tell me until this if it is clear or I'm happy to explain no it's clear it's clear pretty much here yeah all right great great so uh what I'm going to do now so before this step uh since I'm going to use only one particular let's say one particular sheet I will select any one sheet so let's say that I am selecting uh April right because I'm going to apply the transformation in that one sheet and it's going to be replicated across all this that's that's okay and uh I'm going to expand this sheet okay so this is exactly your Excel file this is your April sheet Excel file can you can relate to that so we can we can do a quick check yes right so if you go to the April you see this it's exactly the same the only difference is that since this is not a table this is a sheet it does Auto align a column one column two it does Auto align that so we can fix that now that is the only change you will see otherwise it's exactly the same okay yes so this article the blog which I'm reading okay what they say okay they say that we need to delete this change type step I will tell you why because here if you see the change type step we are using the column names for the sheet right so if I apply these Transformations if I apply this transformation to the other sheets it will try to refer to the column names it will try to find the same column name which is not in our case because our column names are going to be different in in every sheet you will have April 1 April to April 3 in May she will have May 1 May to May 3. so there should be there should not be any reference to the column names so that's the reason why the article uh uh you know it's it's you know in article three steps bi Block it's saying to delete this change type step so that's the reason why I'm deleting it so I mean some of the concepts might be alien to you do not worry about it uh just try to understand things at the top level and I'm pretty sure you know once you practice it on your own you will get it because if if I'm sitting on the other end I'm listening to this listening to this for the first time probably I would not understand hundred percent but uh I think you know it's just like a osmosis process you take the information and go back and you maybe you know watch the video again you get it better all right so probably this you will understand very easily so what I'm going to do I'm saying see this is very intuitive I'm selecting the table I'm saying use the first row as it is because I want this as my header column header okay okay so I'm selecting here and saying first visitors okay and I don't want this I want this gone you know this first row is not selling any purpose to me I want this right so I'm gonna say again remove the top rows and I'm gonna say first row remove the first row for sure good and uh I need to change the column name slightly so this is not uh this I think this should be employee code correct right are they good and this should be named names so what's happening now what the what you are trying to build again just to re-regate whatever we're building for the sheet this transformation will be applied across all the sheets in your in your Excel sheet in your Excel file and you don't have to do this again for uh May or do this again for June this will be that is what you're trying to do we have not achieved that yet we will hopefully get there and uh so like I said finally apart from these two two columns everything else is a date correct this is a date and uh so what I will do I want this dates to be in one particular column one column there is an option for that in power bi so when you select like uh when you when you when if you look at the options in the top so what I'm going to do now is a transformation correct I'm going to transform a data I'm going to transport the table yes under the under the transform option I'm going to just glance at what options they have available and I'm going to select something that that kind of you know suits to me uh verbally like verbally what I want to do so right you know uh you we just discussed about the concept of transpose which means changing the rows to column okay and uh the which is exactly the same here it's called antivirus which is you know it's exactly the same word so what in under and period columns we have three options and keyword columns and create other columns and creatively selected columns so I will say I will select these two columns and say and pivot other columns which means keep these two columns like how it is and and pivot all the other columns okay so bring them to one column that's that's anti-witting right if I click this you will see exactly what we need we have got all these dates under one column okay and we have got this values for example this tanus takur uh on 1st of April is present so we'll get the individual data here yeah it's the same data so if you go to the previous step you can see the same data just the format in which you view is different right initially you had like for this for you will have only one name and you will have multiple columns on the dates so now what will happen is you will have the same uh you know you will have multiple names but you will have one column for the date it's just the different viewing data right okay so and then uh I want to change this to a date I'm changing this to a date and this can be uh this can be value and I'm going to change this to a text but you know what this this is currently in the oh okay hmm oh I didn't notice that we had this uh I thought we only had dates in the in the sheet let me look into this okay oh okay after the dates you also have this kind of some insights I guess yes okay got it got it so we don't need this so what I will do I can remove this okay so you can simply you know how you select an Excel you can simply open this and uh you know you can simply deselect it right yeah and simply be selected and it will remove it but if but what happens if you add a new column right so you have to come to this place again and then deselect it again so we have to think of doing something Dynamic so you know the whole purpose of doing this exercise is automation so you don't have to you know like think about or what happens if you add a new column what happens if some user does this and that right so I'll I I need to think of a dynamic solution now like automatic solution now uh I mean there could be many solutions but the one that comes to my mind immediately is that I'll make this as a date field right so if I make this as a date field automatically all these X values will become errors correct okay correct they they they are not dates because holidays off cannot be a date so it says this is an error so then what I will say I will right click on this column and then I will say remove the errors okay which means in future if I'm getting a text value it will be automatically removed that's exactly what we want okay I mean right now I think this solution will work so if something changes you can we can think about it okay so I'm going back to the blog now so in the blog it's explained after we do our transformation uh we need to do something else okay let me let me check [Music] okay we need to create a parameter now and parameter think about something like uh so let's say I'm saying every month I need a particular day so parameter is is a way of filtering that particular day and this can be dynamic that parameter can change today this is April tomorrow that will be January and day after tomorrow you know it can the next month it can be completely different so that's that's a parameter right it's it's like the the way you limit the data the way you filter the data and a parameter is a dynamic way of doing that so I'm gonna go to manage parameters there is an option and I select new parameter and I'm simply following the the blog it says to create something called worksheet and keep the text and the current value is uh so the current value should be the value of the sheet that we selected so I forgot the value name of the sheet so it's April 22 APR to 2022 okay okay so just to be clear finale if you had a if you had sheets with same names it would have been something that we could have done in like one minute since we does different header names we are doing all this transformation but it's a good use case right this is a genuine use case okay uh all right and what they say after that uh is to okay see here we are doing some heart filtering correct we are we are doing some heart filtering like oh filter this April 2022. so this is where we are going to use a parameter now okay we're going to use instead of saying directly filter April 22 select it based on the parameter worksheet which is the worksheet parameter so now uh take a step back and just think about all the steps that you've done right so what we have done we have imported this data right we have imported the April data uh we promoted the headers and uh we did the anti-voting so basically we we did some transformation steps to the data so now I want this transformation to be applied across all the you know to be applied across all the uh all the sheets yeah so I need to create this as a function let's say that uh I'm if I'm trying to explain it in a very simple terms let's say that you add number five to a particular sheet no particular sheets data and you are you want to add number five to all the sheets right so you need to create that number five as a function so this function can be replicated across all the all the entities that you have all the sheets that you have right so what what we're going to do now is something nice something interesting I'm yeah so you can create this entire query whatever Transformations you have done as a whole function so this becomes a new function now so double you have a easier way to explain this you think uh yeah basically what it's like a template we have created so it's like a process number of steps that we are applying on this sheet we can apply the same thing on a different sheet so we have kind of encapsulated all the steps into one entity and when you design it as a function you can just apply the same function on other sheets as well so basically you don't have to repeat it's just a reusability right of our code yes exactly reusability of the code that's that's the right word that's what I was looking for okay so uh okay so we have got the function so you see basically what we have in the function is exactly the Transformations that we did so if if you have to type all this code if I've never done it so it's uh we're lucky that we have to do this uh clicking drop buttons and get this done okay so now I'm going to the original sheet which we have this is the original sheet and uh like I said we don't need this attendance key I'm taking this out yeah so what I want to do now uh is I'm going to check this one okay so this basically same add this function as a column right like a new column so if you go to add column invoke the custom function so we have created a custom function we've got to invoke it the invoke means like we are going to activate it call this kit data and also call this this is the function so you see the function name is gate data so you have it available here that's exactly what you get and you press ok ok so we get errors uh that's really funny okay why do we get errors we have got the April data and you're not getting the okay so you see what happened here is trying to reference a column this function is trying to reference the column okay the block Lily said delete all the steps which tries to reference the column because it should not reference a column which means that you know it will it will try to look for that particular column in this in the mail data so if it is trying to look for uh first of April in the main data of course it won't find it there is no first of April in the mail data so I'm going to go to my template and check where I have referenced the column uh uh okay you see this step this particular step this change type step it's referencing all these column names yeah this block says in block letters to delete it I forgot to delete it that's my bad so I will delete this and hopefully it will be good let me check okay you see now you get the data yes you see it's it's it's uh it's all the data is available here I think we are almost done so let's expand the data here so you remember these are the four columns that we have employee code name date and value so we have the exact four columns I'm just going to expand this and uh I will delete all the rest I don't need all of them maybe I will keep this item because this this will this will tell me the sheet name which sheet the data is coming from you know just from your reference so I will delete this this this and this yeah so I selected this using control and I selected remove columns and uh so this I'll change the sheet name and this is employee code and text and this will be just the name uh so I would just change the names and and rename the types I will first rename the columns and then change the type together so you don't create multiple steps okay and so I'm changing this to date changing this to text this is text again okay I think this sheet looks clean uh before loading this to the power bi where we can create the visualizations I want to double check if everything is good so so right now I've selected June okay I see only the June data right good uh I want to check something randomly so let's take in the case of uh a Loki Lal okay lokila 10th of June is present yeah let's go to the Excel file uh not this one this 10th of June showcase present so that data is correct so likewise you can do some multiple checks to ensure your data is correct because uh you can random randomly check the data just to ensure that you know everything is correct everything is flowing through right and uh so this is this is one one level of checking of course you know you will also check it again once it goes into the final uh you know visualizations and things like that so what I'm gonna do now so to me the data looks clean and uh I'm gonna clear the filter and also check once because I know I applied the transformation only for April I want to check how the mail looks right now it's like June I will also check how the nail looks okay may is also looking fine okay why do we not have first of me okay let me check oh yes because I guess the month is um yes it was Sunday or something okay yeah okay good point yeah I mean you can add it in the sheet of course and then it will reflect here okay it looks good uh I like what I see and uh see and and before loading the data see what we are going to do now uh I'm going to load the data to the file now to the power bi and I will not load all the data I will just load only what I need so I'm gonna disable the load for this template I don't need the template I've disabled the load and uh this one does not look correct the name I will change this to something like combine the data or something or let me call it final data okay yeah okay so uh let me press load and uh so it's going to load the final data here yeah it's something interesting yeah so you know so right now what we did we basically had like this cumbersome Excel files like in three different sheets and all those things and we got it into one usable format right which is this one you can go to the final data okay you see that sheet name is you know this is this is not necessary I just kept it for you know just for reference purpose basically just keep these four columns and uh you know you have it in one particular form and all the dates are here so now it is this is very easy for power bi so whenever you have dates in multiple columns try to bring them in one column then it becomes very easy for power bi to you know to do the transformation to do the formulas and all those things if just imagine if you add it in multiple columns you need to create like one formula for each day and you have to combine them together that is humanly not possible you need to bring all the date values to one column whenever you see date values in multiple columns just remember this you need to bring them in one column this is like this is something you can do as a reflex States in multiple columns will not work it won't work you know you will understand that more when you practice but just for now this is a this is the takeaway I think you should you should think I hope you learned how to import and transform data in Excel there are few takeaways from this session Googling Your solution is one of the most important skills whenever you are trying to transform data think of a dynamic way to do it so it works with new data as well take a break to reflect and digest the concepts if you feel overwhelmed are you ready to step up to the next level see you in the next session in this session we will learn how to create metrics what are metrics they're nothing but a quantitative assessment which will help us make decisions if you remember pinalis shopping cart it had three items number one working preference of people between work from home and working from office number two percentage of overall sick leave to understand to monitor employee wellness and the third one was learning power bi Basics through this project see I think she's already doing that just like you the first two items in her shopping cart are nothing but metrics also called as measures in the power bi context the first metric you will learn to create is the percentage of work from home the second one is the percentage of sick leave but there are more you will see that during the process how a data analyst can add more value by helping the stakeholders to discover more metrics all right so since we got the data in the table now we're going to create the measures that that we want to you know see in the dashboard okay I would recommend to keep the measures in the in a separate table so I'm going to call this like a measure table just to summarize him and click on that enter data button on the Home tab and he's just entering the data manually yeah okay so so first of all I want to you know the whole thing is revolving around the fact it's it's about a percentage so where we have the you know the ratio to the total working base the the number of number of present days versus total working days number of absent days this is total working days number of uh work from home business present days so the first thing I need to know is the total working days so I'm going to create a new measure and in the total working days should be uh should be I think count it should be the count of all the values right basically all the things you have in the value but if I if I simply do the count I think it is just giving the total days but I want I think we need to take out uh I think we need to take out the weekly off ers so apart from this everything is a total everything is a working day everything is a working day okay so we'll go back to my power bi we're gonna make this total days this is total days I'm going to create a variable I'm calling this as a variable and uh I'm creating a second variable which is like non-working days on work days and uh so this is again going to be the count of you know the values but in this we need to remove can you increase the font size human and maybe controls scroll up yeah oh yeah that's a good idea I'm just finding the non-verb days which is uh forgot it again what is the code it's w o and H O so this formula basically is saying count only the values which has W and H O and uh so finally when you're working on this stuff like this you you need to understand how the calculate function works that's very important like if you understand how the calculate function works you can do 80 of your work right okay I'm going to return return the final value which is going to be total days minus non-verbase so folks deadline number five uh can you can you open that again so line number five is more like sql's where query where you are saying get me count of final data value where final data is either wo or ho it is if you know Excel SQL little bit it is short of like that that inner portion is like SQL where query yes that's right okay so if you just drag this all you have to do is like just drag it to the canvas here and it will try to build a measure so you have the total working days which means you know the total working days for all the employees in this four months right it's it's like a total sum and from the total working days I want to understand uh what do we need to do I think we need to understand the present days the number of days the people are present correct so I'm gonna call it present days [Music] and this is going to be quite simple use the same formula that we used before calculate the count of value but I want the values only in uh I saw I I think I saw its speed right present days is p correct yes okay uh okay let me think about it so if I add the present days so we also have work from home days right work from home is considered as present yes okay and we also have half work yes yeah so I cannot use this formula because it will consider half work from home also as one it's not the full working day so we need to we need to do something different let me let me do it so but let me just add it as a you know well you're trying to do prison versus not present so our definition of present is even if person is working half at home half in the office yes for that day the person is present so present yes one yes yes yeah so let's say if they are working only half work from work right and let's say if they take half Secret okay so that is like half a day present right so if I just add it here it will consider us fully present so I have to be I have to be very clear in saying that uh just give me you know just calculate the present days [Music] uh where the value is equal to p but this formula is not complete we also need to add the work from home base to this [Music] okay so since uh so if I use the same approach here so what it does is like it calculates it counts each value as one so if there is p it is one if there is a second P there is two uh if I use the same approach here there is no there is no way to say here like if there is half work from home calculate this is half we can do this in tax but it will become very complex so I will I mean if we can do this in a Dax measure but it will become very complex so what I will do is like I'm thinking to create it directly in the in this table I think that will be easier for me I go to the final data table I will create it here so here I can easily say if it is uh if it is uh work from home write one if it is half work from home write 0.5 then as a column to me to summarize to me to do the summation on that column it will be easier so I'm going to create a new column saying here and call this work firm home count and this is going to be uh I can use a simple if statement if value if this value is equal to P if this value is equal to Hope from home and it's one okay so this is this is why I prefer to use switch statement because I I don't like writing multiple lists so I would rather write an switch statement so which is a good alternative to if and uh if it is half cooked for me should be 0.5 and for all the rest of the values let's let's have zero is it correct by definition should we do this yes so you can see for present it's not considered as work for mode so it's a zero but uh when you see let's let's do a quick check if my formula is working correct so we're now off work from yeah you can see it's 0.5 and when they have work from home it will be one perfect that's that's what I need so I'm going to create a new measure here calling Hook from home so now this is the easy measure all you have to do is like just make a sum of the work from home count column and like I said I need to adjust my present uh days formula and uh I'm just adding or or I don't need to create a variable since I already created a measure I don't need the variable I'm simply adding the work from home account here okay so I'm just pulling the present days here so this kind of looks correct because of course your present day should be less than your working days which looks quite correct to me and uh I already have the first thing that you got that you want which is okay um do you want to call it presence percentage yeah that should be yeah nothing but your present days divided by total working days and since this is a percentage I'm making this a percentage so you can feel you can think this is an Excel formula how you write an Excel formula like some Max and all those things you just have to learn the basic functions it will help you so once you once you practice this on your own you will have a better understanding so this is our overall you know presence percentage that that we have got and uh to to kind of visualize things better I'm gonna add this slicer and drop the date uh column so this is a big column okay this is the reason so you see that we have all these days data's days like each day so I I cannot select I can sell for each day but let's say I want it as a month right so I'm going to create a new column here as a date month so I'm going to call this format date value I'm going to say start of the month date value so this kind of gives me the start of the month and I just go to the format here and say mm YY so I get this like short format okay so instead of dropping the date now I'll drop the month okay okay then you can change it to horizontal so you can have it like this so you can select now for April and then see the person's percentage you can select from a and see the presence percentage and then the June so these this forms you know the basic measures that we will use and uh I think the next step is to build the dashboard out of this you know and we can build some measures on the Fly because we have got the basic measures and we can build some measures on the fly as needed for the dashboard I hope you had a good introduction to creating Dax measures in power bi there are a few takeaways from this session number one is group your measures under one place the second one is understanding calculated function in Dax is very important and the last one you can also create additional columns to your data using Dax I'm excited for the next session and yes we will be building the dashboard in under 30 minutes see you there this is it folks you have reached to the most rewarding part of this series you will be creating a final usable dashboard in next 30 minutes most importantly you will learn some dashboarding techniques which is very important skill to acquire do you know in this session you will also get a deeper understanding of HR domain by knowing how this metrics metal them all right let's just get started with our dashboarding okay so this is uh like I said this is where you know we get a dashboard now uh so you we wanted to at the top level we wanted to understand the presence percentage and we want to understand the work from home preference for people which is like basically the percentage of time people are working from home okay so to create that I'm going to create a new measure I'm going to call it work from home percentage I'm gonna say divide work from home count so this one we are not going to divide this with the total working days we are going to divide this with the total present days because you want to know out of 10 days when a person is present how many days that person is working from home like if the answer is 20 it means out of 10 days two days the person is working from home so the reason why I'm going to as you could you can you can learn most of the things from the syntax itself so the reason why I add 0 is an alternate result for example whenever there is no result you will get zero you will not be thrown an error okay so I'm going to copy this the easiest way in power bi is like just copy paste the visual like copy this visual and you click this option you replace this visual here so you don't have to redo it and I'm changing this to percentage so [Music] so what what we understand from here is like so on a given day if you have 100 people working in your company 15 people will be working from home that's that's the Insight that you can get this is this is for the entire month of June can we can you look at other months yeah okay may is 11 percent that's okay this is a pretty pretty good Insight basically because uh this is real data right and as an ethnic co-founder now I'm getting inside that in June the percentage of people working from home is higher compared to April I am also noticing one other thing in real time which is this presence percentage can help us build a a proper schedule for our releases for clients usually in the Diwali months right which is October November more people are absence so let's say if we have this insights then when we take up projects from client uh we will say and if they have some release schedule around that month we can tell them that look we have around 70 people optioned in these months hence we can do our release planning properly correct now as such you can use common sense look Diwali comes in October November and most of you see like 30 to 40 percent people they take a long like few days of time off right so you can plan things according to that general knowledge but yes there are some uh seasonal uh behaviors which is let's say if significant percentage of our population is from Patel community they have this marriage and waiting season uh right from December all the way to till 15 January and those Trends can be captured in this dashboard yes right and then we can have these like hot zones where where more people are absent during the time of the year and we will do our release planning client like software release planning according to that okay so uh so after presence percentage work from home percentage you also wanted to understand if there is anything like a covid spreading you want to understand the sick leave percentage as well so I need to do the same thing what I did for work for work from home right I'm I'm simply going to copy this from Love so it's all about reusing what you build and trying to you know like get the best to creating a new column now okay instead of saying uh work from home I'm gonna change this to SL and do we also have half cichlid correct yes absolutely HSN hsl perfect so this is going to be a cell count which is sick leave count so let's check if it is correct uh select so should be one and if I select uh half sick leave it should be zero point perfect yes that's working and uh again I'm going to come here and copy this formula [Music] and I'm going to change this to SL count and this is going to be a cell count so everything else is the same and uh copy the formula again instead of fork from one percentage you create a sickly percentage and just for the viewers what Amazon is doing right now is he's creating new measures in Dax so Dax is a query expression language which in power bi and you can use this measures which are very useful when you are building the actual Visual and the dashboard yeah so for the sick leave percentage I think you need to understand exactly based on the total working days right yes okay perfect now we use the device divide function here you can use this General divide operator but this is a safe operation if you have divide by zero situation which is undefined operation in mathematics so whatever if you have uh divide by zero situation it will give you a zero value which is a third parameter in this divide function yes yes okay so I'm gonna copy this visual again and put a secret percentage and changes to percentage okay percentage is very not really you know like significant which is good so yeah [Music] see there are two ways you can look at the sickly right you want either you want to know out of all the leaves people take what is the percentage of sick leave in that right that is one way of looking at that the other other way of looking at that is you know out of the total working days what is the percentage of so I have done the second one now but if you wanted to do the first one we need to do different calculations but since you want to understand you know like the pattern of sick leave and all those things I think this is a measure we need to use it is so so for someone who's learning dashboarding I would say always keep the most important thing that you want to see in the top left right and uh because we write we tend to like right from left to right but there are some Nations which write from right to left so they would do the opposite but for us uh you know in India especially in most countries they'll write from left to right so we keep the most important things on the top left so that immediately draws your attention that's where you see immediately your your nature look there and uh okay I'm gonna reduce the value a little bit category label and the callout value as well maybe make it 22. okay I think this is still fine so now first thing the the way of building a dashboard is now you have seen the dashboard right you you you have this inside 14.2 percentage work from home if you want to ask why you want to understand uh you know who's doing work from home like perhaps you want to understand which day most people do work from home so I'm going to under add an underlying data now which is which will help you with it why right and let's say that uh I'm gonna add a table now a simple table and it's a table visual that you can see select from the visualizations and I'm going to drag the name here so it will give you all the names and we will add all the top metrics there so you see the same thing that you see in the top by employees now and you have to work from home percentage and you have the sickly percentage so by employee you have all this all this metrics now so maybe you have 100 work from home roles so you have that you know people who are 100 work from home uh you know people working 63 person work from home 46 person work right like half of the time they are working from home in June and uh if you want to understand what's the pattern you know in other months we can do that we're going to do that shortly and so this insights I'm Gonna Keep it here [Music] so in the top area I would recommend to keep your you know companies logo that that kind of okay let's see The Branding and we can also enter the name of the dashboard I think it's it's you want to call it like presence insights yep so what's your favorite color finale you know hello okay okay the presence is hopefully I think it's hopefully present okay that's my favorite color too finale oh wow yes I love it like with minimum so here you can see you know like uh the person's percentage if you you know if you just click this value it kind of sorts from ascending to descending and if you click again it starts from descending to ascending so you can see here like somebody's present 40 percent 60 percent maybe you can think about it like why you know this this can be the correct number maybe they have some paid leads or maybe they even they might have left the company right that I don't know like whatever the thing that is so you can you can you know this will help you quickly to have insights with the people and uh so let's say a particular person this is Giovanni Pope is percent only 66 percent now you want to have further insights right you want to see that even sheet of the person so that's what I'm going to add in the bottom now I'm going to add the entire attendance sheet in the bottom okay so basically I copy the visual and here I'm going to change this visual to Matrix rather a table correct and uh I will remove all these values because we want attention sheet I'm going to put the value in the values which is the the one that we got from the attendance and I'm going to put the date the date the days not the month uh in the columns so there you go so if you want to understand more about Giovanni Pope you click Jovani Pope you see the full sheet full data you underst you see you know the data starting from 1st of June you understand okay right right and uh if you want to understand about rotary go you select the data you see that you know you get more insights the granular insights so if you if you had to look into each of the person on the Excel sheet I think that's the reason why you want to create this dashboard you want to narrow down to the areas which you want to focus more this is pretty interesting uh can we look at someone who is 100 absent I want to figure out if that is because maybe they left to come this is for June right let's say they left the community on 5th of June and last few days let's say they were absent is it that or is it some other reason okay so let's check this first one right zero percent presence zero percent sorry yeah not 100 but zero percent presence yes yeah so zero percent present iron Atkins and uh from home what is this HL WP live without me where they don't have the balance okay got it so it's it's right the data what it's showing is right so okay there might be some something that you want to analyze on this understand all this maybe you already know about this you know you can yeah that will help you further in case you don't know about this then it helps you already because you you this came to your uh you know this came to your attention if you know about this that's fine yeah so you can do all these things here you can ask more voice and get to the granular level of data but you know what what you see you would still need a trend right because here you can't click every time for a particular person and then you know like find this thing you need an overall trend for each of this insights for the presence insights work from home insights and the and the sick leave percentage do you think that's do you think that would help you I can I can add this to the to the right um I think this is perfectly fine for now okay I mean I would I would definitely like to see the trend because I spotted the trend by looking at individual months is that okay work from home is kind of going up but that's like I have to individually spot it if you can maybe can you draw like some visuals some line charts something that can show me the track yeah yeah I'm gonna I'm gonna create uh okay let me take a line chart and uh so it's it's it's it's going to be really easy because we already have the Matrix so I'm gonna put the sick leave uh no I think presence percentage right for instance percentage here foreign [Music] so I've got this as a table now so I can easily change this to any chart I want for example I want to change this to you know like column chart or maybe uh area chart and you see the reason why we get this more like a sine wave is because why is that okay yeah it's weekends okay correct yeah it's every after every five entries we get a reaction so what we can do is that there is a very easy option to do this I can go to the value here and uh I can apply the filter on all the pages and I can remove the weak and I think the weekends are denominated or num are mentioned by week off so take this out so you see now the curve is better Yep this is much better yeah yeah and uh I would also like to add a trend line here so you understand the overall trend so I'm just wondering why there is a big drop here 50 percentage persons that doesn't look correct to me before June 27 June okay okay I think there's something wrong because we have the today is 26 June and we have date until 30th June that's not correct let me check the Excel file once until which date we have the data because uh I think I received the pinalis email like three or four days ago uh okay oh so I think the is still 30 June but there is no there are blanks there's no data okay so there are blank cells so what we can do uh with this situation is that uh so we can apply a filter ideally you can apply a filter here saying that uh your date should be either today or tomorrow because we are going to update this sheet on a daily basis right so you can do that uh like you can go here and say Advanced filtering oh no sorry relative date and you say is in the last let's say 365 days which also includes today so it won't it won't take all the future dates that's the filter you need to apply but since we know that we have the data on only until 19th of June I'm gonna I'm gonna use a you know like like a heart filter just for this case just for the analysis case but we need to change this so I'm going to say on or before 1906 2022 or I will just use a date picker spinner this is so amazing I did not know power bi has all these capabilities usually you use the last 365 days if your data is up to date yeah it's yeah okay I have data until 17th of June okay I'm gonna say 17th of June okay it's much better Okay so to be I think adding a trend line would help because so you understand the trend better and I don't I'm not really caring I'm not really concerned about the y-axis because I don't want to display the I don't think it makes it makes you know I think I think you just won't understand the trend right with the with these pointers can you unselect June 22 can you see like monthly I want to see a trend for last three months okay oh nice you can see that the presence is slightly declining declining there might be something yes some some event like some seasonality to it yes I don't know we need to figure out yeah yeah so this is Presence by date so now you know for me it's going to be very easy to duplicate the rest of the stuff uh to create the rest of the stuff I mean so I'm gonna I'm gonna copy paste this and uh changes to work from home per stage it's kind of increasing trend and just to make a differentiation I I'm thinking to change the color so in order to activate this box you know like if you you know if you just look at there you won't find the box so in order to activate the Box you need to select a visual and then see you know then you have the settings and all the stuff so under colors you can change this yeah if you have a question you can unmute and remaining time you can stay muted because I was hearing some audio disturbances so yeah if you you want to speak you can unmute okay so and the last one should be the SL percentage I think we almost got our dashboard ready this is so nice I like this whole copy paste thing like it takes time to build some initial visuals but after that it's just pretty much copy paste yes I love the dashboarding part because it's it's the it's the time you know it's it's where you know you get the things completed really fast and most of the the most amount of time is spent in data cleaning as you know and dashboarding is really cool yeah it looks really good yeah finale must be happy seeing some charts and visuals rather than looking at those big extra files with numbers right right I I really agree that okay so see I I already spot some insight that as June month is approaching for starting from April presence percentage is declining person with people working from home is increasing right sick leaves are kind of a little bit increasing maybe it's the summer months it's a very hot summer months because of that so now if we have some software releases for our clients around June now we can you know plan those things better that the our capacity is kind of lower compared to other months during this June month so we do our release planning accordingly yes that's true okay and uh so you see that the trends increasing and decreasing that's that's really cool and maybe we can add more uh you know insights to this and I'm thinking one more insight quick Insight which I can which I think we also discussed is that uh I mean do you wish to see the numbers here like I can add the data labels does it does it add any value I think so right if you if you know the numbers I think that kind of provides you a yes context especially when you go to what I like to do is I like to go in a focus more so can you go in a focus more for one of the charts there is especially very useful see now you have a big visual with all the numbers see there is there is a huge you know one one thing that we can do now that will really help you I believe is that to understand which days people work from home the most which days people are present most are absent most uh which day of the week right so for that I am going to add the day of the week here very quickly so I'm going to add a day of week I think what we need what we need is just change the format of the day that you already have so we have the day and I'm going to change the format to uh if yeah this will provide me the day in three three letter format yeah you can see that half the Monday of institutes is tough now and uh if I put the day of the week here and I can add like presence percentage can you make a guess which is going to be the day where people are present the most Monday I think Monday or Tuesday Tuesday you're correct people are present the most in one day yeah because they're going out or we're taking vacations they'll take like Thursday Friday sometimes Monday also and they just combine it with the weekend yeah people are pressing the most in one day and uh let's say which day people take work from home the most I think the answer is something you already know it's gonna be Friday I believe but just to understand the percentages it helps yeah Thursday and Friday people take most work from homes like if you have if you have 100 people working on a particular day you can expect that 15 people are not there on a given Friday that's that's an insight so if you if we are arranging any team building activity team lunch Etc maybe we can do it on Monday where most of the people are present yes yes that's a very good insight yeah and this is I think this is irrelevant to the day but still you know just to keep the Harmony in our dashboarding yeah it's Monday okay I think it's it's pretty much the same it doesn't make sense you know to check it by the days okay so this is I think this is almost this almost good as a dashboard I know I know this is not really looking beautiful I can make some beautification you don't have to you know I can make make some beautification changes and send it to you but as a skeleton as a layer do you have anything more to add here no I think they have covered everything here one thing you need at the moment but I think after start after you start using it you might have four requirements it's not yeah so maybe we finally you can start using it and then uh we can think about uh enhancements or improvements for the next phase you know as you start using it maybe you'll have more questions and some of the questions will not be answered by the visuals that we have that was indeed a rewarding session here are the key takeaways from that session number one where to place your most important insights in the dashboard number two good Insight report should enable the stakeholders to ask more whys number three copy paste visuals and alter them to boost your productivity usually this product is called MVP or minimum viable product which the stakeholder will use and provide more feedback so that you can build next version of the dashboard let's see how our analyst captures the wish list from stakeholders for further builds in the final session of this series we have created the dashboard but our stakeholders want more they have a real-time business needs such as sending alerts automatic data refresh setting security levels for data you will also understand how a data analyst Scopes the requirement for future bills from this session okay so now we've got the dashboard if you have any questions you have any anything to check I have one like can I get any notification over my email ID like just like it to us should say if the attendance is less than 80 percent or 75 percent because uh um actually I'm very busy and uh these days right I don't have time to uh see dashboard each and every time it would be easier for me to understand the percentage Yeah see totally understands what you can do is like the next step is like we will publish this dashboard uh to the cloud okay so once we publish the dashboard you get an option there to create uh like you know this is this is you know in the power B8 language lingo this is called report so once you publish this import online you can create certain dashboard from the visuals so you can create a visual this visual you can add to a PIN to a dashboard and from there you can create an alert so if the presence percentage is less than 70 send the email to finally like uh to pay your email address and do a couple of other people so that email will say the present specification less than 70. uh here's a list of people who are present today and also you know it will send the Excel sheet along with you this this particular thing along with that so we can yes we can do that it's possible that's great that's really interesting because even I would like to get that kind of notification yes uh and for the viewers uh we have covered publishing reports to power bi setting up notification all of this in our paid course on core Basics dot IO that the course name is get job ready uh Power bi data analytics so in that course we have covered all of these advanced concepts the link of the course you'll find in the video description below so check it out or just simply visit codebasics dot IO all right now moving on to our actual discussion so I know I'm kind of taking uh I'm playing two different roles double the instructor for codebase channel and atlix founder and manager uh so one question I have is right now we uploaded one fixed file okay but finally keep on uploading so I don't think it's it's going to be very cumbersome for at like HR person to give you this file every day can we have some kind of automatic setup maybe we put file somewhere on cloud and just definitely that is that is definitely the point so either you know probably offers a simple directly just to show how it is done but ideally you know it can be put in a SharePoint folder a common drive and I think if this is a Google sheet there is also option to directly connect to a Google sheet I I think yes you can also connect to a Google sheet directly so if it is updated this will get updated you know it's it's uh there are a lot of ways to do that and uh normally what I would prefer is to put it in a SharePoint folder and and it is connected to the SharePoint and the data gets updated every one hour or every half an hour or something like that we can set up things like that and uh no one needs to you know like or you know like do something so once you've created this and it's it's completely automated that's the solution all right next question I have this is pretty useful by the way so yeah using SharePoint is a good idea next question I have is I want to make this dashboard available to people in my organization obviously manager level people will have access of full dashboard but employees so that they can see the trend Etc they need to have access of only the charts because I don't want employees to look uh you know check the presence percentage and all of that for other employees you know it's not very healthy work environment yes so can we have that visual level privileging yes there is a option in power bank uh that's called row level security and even object level security there is an option there in power bi we can do that um there is a there is a better option in terms of you know because if you create this dashboard and if people are saying blank space here it does it doesn't look correct it looks taxi right is like publisher dashboard publish this dashboard for the management level people and uh give access to them so once you publish this dashboard on the cloud this creates a data set underneath so this dashboard or report sits on a data set so we can use the same data set on the cloud to create another dashboard with with just the trade not the not the underlying data so that is that is you know that I think is a better idea so there will be two different reports but both will be using the same data source same data set everything is same it's not like there are two separate entities they are the same entity but two different things got it so like you have presents you uh right now right so you can create presence you for all and all we have to do is just copy paste visuals so it is as as good as that very good yeah all right I have one more question I got pretty excited by the way after looking at this uh dashboard so the next question I have is I'm looking at the chart and most of the people they prefer working from home on Friday so finally maybe in the future like when we go in this hybrid work model uh we can have Friday as black or from home for all right and if people are working from home on that day then if you have some should do to maintain and some electricity work or you know some infrastructure repair uh because you do that kind of work on Friday usually we do Saturday Sunday also but then you know that can be the good utilization of resources other thing we can do is when if you decide to go and hybrid work from home model where people are working only three days they're working from home on two days maybe we can ask people to take turns so that let's say there are the capacity of our office let's say is 80 people and we have 100 employees maybe we can ask them to take turn on a day so that we don't have to rent space for 20 additional people we can have people take turn and create a system where people come on Alternate days and we can save on the rental cost all right great those are the only points I had uh thanks samanan for helping us build this life and we are going to provide a link of this dashboard the data everything in the video description below so folks go check it out if you have any question Post in a comment box below perfect it's a pleasure thank you thank you very much so how do you feel if you are HR manager yourself you probably found this useful because this was from your domain but even if you are not from HR you still found a project that you can use to learn data analytics and this is something you can put on your resume as well now I have a good news to share just like finale you can also come on this show and we will help you build live data analytics dashboard you can be from any domain you can be chartered accountant you can be a salesperson a warehouse manager any domain wherever there is data there is data analytics so all you have to do is check video description below there is an email ID send us an email and mention your use case what kind of data set you have what kind of business problem you are facing what is your domain and we will invite you on this channel so that way you will learn data analytics live with us and also you will get an apportion reader present yourself on this YouTube platform I hope you found this series useful and I will see you in the next data analytics series foreign [Music]

Channel: codebasics

Views: 93,159

Keywords: yt:cc=on, data analysis project using power bi, data analyst project, power bi project, power bi projects for practice, data analyst projects, data analysis projects for beginners, data analytics project, data analyst projects for beginners, data analysis projects, codebasics power bi, data analyst real-time projects, power bi real time project, power bi project step by step, data analysis resume projects, data analytics resume projects, power bi project for resume

Id: JC66t9eM10s

Length: 96min 4sec (5764 seconds)

Published: Thu Sep 07 2023