TOP 5 HR Metrics you SHOULD BE TRACKING and How to Calculate them in Power BI

in this video i'm going to show you five key metrics that you should be tracking if you're working with hr analytics and also what kind of data you need in order to calculate them and also how to do them in power bi all of that and more so without further ado let's get started hi my name is fenan and welcome to the solutions abroad youtube channel where we cover tips tricks and best practices when working with power bi i upload new videos every week so make sure you hit that subscribe button and the bell icon to get notified when a new one is out hr metrics or human resources metrics is a key part of the business and it allows the organizations in the business to have a pulse on how the organization is doing based on their sort of human capital management maybe they want to know how their workforce is doing maybe they want to know how many people are leaving or why people are leaving these metrics are important to give a general overview of the workforce where our strengths are where weaknesses opportunities for improvement and trends that have happened in the past and what could happen in the future now in this video we're going to go through some of the key metrics that in my perspective you should be tracking if you're working with hr analytics now there's a lot to cover when it comes to hr analytics so to keep this video short we're gonna just keep it to five and the examples that i'm going to use i'm gonna try to keep it as simple as possible so you can follow along also bear in mind that some of these metrics have different variations uh depending on the business or the organization that you belong to so just keep that in mind when i'm going through the demo so the first metric that you should be tracking is the headcount which is essentially the total number of employees that you have in your organization there are a couple of different variations for this but the simplest one is to just count the number of active employees that you have so for this we're going to move to my demo you'll need a list at a minimum a list of your employees and a category that distinguishes whether that employee is active or inactive so here in this demo we have a table here you see this is the list of our uh fake employees and you'll see that we have them uh active and inactive now for you to get a count of this is actually pretty simple you don't need to write any dax code at all so we'll just create a new card here we will go to the table and we'll go let's say i want you to count the number of employees in this card we'll change the aggregation to account so it gives us everyone but we just want to count the number of active employees so we'll drag the employment status on the filter filter on this visual and then we'll check active so that will give you the number of active employees out of that table so pretty easy right but what if you don't have the active or inactive status you just have let's say a start date and an end date now you can do this as well but it requires a bit of a setup so here's an example of what i mean you can see here in our table we have a list of employees from our system we have two informations about them we have their hire date which is when they've started and when they were terminated on the right hand side here and think about the type of calculation that we need to do here so first what we want to know is if that employee is an active employee and active for our perspective means that their start date was before the current date right now but then only that that the termination is either in the future or that it's blank so it means this person is a current staff so the first thing that we'll need to do is we'll need to create and use our calendar table uh to create an inactive relationship between them and i'll show you why you need that uh later on um but for this demo you will need to create a calendar table which i've harped about in the past so this is how you do it there's a code there however if you want to know how to do it yourself and why you should do it i covered it in a separate video anyway now we're going to go to the model view here we're gonna bring in the head count here this is the table that i was showing earlier and then we'll bring the calendar and we'll create a relationship termination dates and higher dates we want to keep both of them inactive at the moment so inactive means that you will see that the dotted line for both of these relationships next we're going to go back to the report and create a new measure so i'm going to go here new measure we're going to name this one headcount pretty simple so let's start writing so let's start with calculate first because we want to add some filter context to this for our expression we want to count the employees so we're going to write count me the head the employment ids in the head count table and then here is where we add the filter context so this one will be higher date is less than or equals to calendar dates and we want to do something like this termination date is greater than calendar dates or if the termination date is blank so it's exactly what i described before it is what this calculation does so it counts the employee if their hard dates is in the past and if their termination date is either in the future or if it's blank so that's what it means for a head count or person to be active it looks a little bit confusing right now so i will just demonstrate to you what it does so i'm going to drag this measure into my report here so it gives me a 497. so this basically says that currently we have 497 active employees in our system however how can we validate this though so we can do that actually pretty easily so um remember we created that uh inactive relationship uh so we're gonna use the calendar table now the one that we've created in a relationship to so we're gonna have a year month there and then we're gonna drag the head count uh but also what we're going to do in this table that we have here on the right hand side we're going to add the headcount measure here so it will show one if that is an active employee and depending on the row context so in this case you will see for example this person is a head count it shows one because this person has a higher date of in the past and their termination date is empty so that means they have not left the company yet and you'll see that that number adds up to 497 so that is how we prove this so let's change the filter context here so let's say we can see here in december of 2017 we have 279 headcount at that time uh so how do we know if that is correct so we can click that uh the year month and it will give us two nine two seven nine and then we can just validate it here so what we're looking for are employees that have a higher date of before december 2017 and that they are either have a termination date in the future or they weren't terminated uh during this time and you'll see it sort of matches up to what we expected here so you'll see for example suzanetsa has a higher date was hired two years ago 15th of november 2015 and termination date is in the future in 2020 so this person is a head count for that year month and also that makes it valid so the next metric that you want to measure is the offer acceptance rate so this is a great metric to track the offers that are being sent to candidates and how well they're doing the offer acceptance rate calculation is actually pretty simple it's just the total offers that have been accepted divided by the total offers made low offer acceptance rate means that we are pushing out a lot of offers that not a lot of candidates are accepting so this metric could be used to potentially look for improvements and opportunities for improvements when it comes to the competitiveness of the offers or the whole employee experience so at the minimum you'll need a candidate level information so candidate names and their offer status so if they accepted the offer or they're being offered or if they rejected the offers and if you have other data associated with those type of offers so the offer date even better because you can even dig through the offer acceptance rates per date so let's create a few cards first to just validate and just make sure we know what we are calculating here so we know that we want to get the total offers that have been accepted so first what we're going to do we're going to go application id we're gonna put this in a card and we're just going to count this first of all so it gives us the total number of applications that we have and then we're gonna filter this by offer statuses and let's say we want to just give me the number of offers that have been accepted so 187 we also want to divide this to the total number of offers made so we'll do the same thing once more we're gonna add another card here do application id i'm gonna do count once more offer status will filter everything so give me everything except uh offer accepted and actually we want to filter out not offered as well because we want to just understand uh how well are the offers that are we are sending out so not offer means we haven't set them yet so we will just uh exclude that and basically the offer acceptance rate is this number 187 divided by 592 which if i use my trusty calculator here 187 divided by 592 will be about 31.6 acceptance rate so now that we know the number that we're looking for let's try to do this calculation in power bi so let's create a new measure to keep it simple we'll name this one offer acceptance rate and then we'll create a few variables to hold these these cards that we have here so first we're going to create one for offers accepted which we will do by doing a calculate here we want to do a count of so we want to just count the applications where the status the offer status is equals to offer accepted next we want to create another variable for the total so we want to do of total offers is calculate again we're gonna do count application id and then we're gonna say if the offer status is not equals to not offered counted so now that we have those two variables let's return and let's divide these two so we'll create a divide and simply offers accepted against the total offers and that should give us the value that we want so if we do this so we have an offer acceptance rate of 0.24 so 24 acceptance rate which is a little bit different from the value that we have in the calculation here so let's try to figure out why so it looks like here we have the offer accepted 187 and ah here we go so this is the problem so i also excluded it seems like it uh offer accepted here from the total which should be seven seven nine so what i think will happen now if we try to do 187 divided by 779 it will give us 24 which is exactly what we have here in our measure so what's even better is because in this example we have offer dates as a value so when the offer was made this means that we can look at this offer acceptance rate not just as a whole but as a breakdown in different periods of time uh so to do that uh we need to create a relationship between the calendar and our table here we're gonna go back to the model view here we're going to drag in offer acceptance rate and calendar we'll create a relationship between the dates so now we can use this calendar table as our filter slicer so we're gonna add year month here so now that we have that we can simply add offer acceptance rate measure so here you can see the offer acceptance rate total 24.01 and also breakdown of it over time so in this table you'll see the breakdown of the offer acceptance rate per month so you'll be able to see which months had the low offer acceptance rate and sort of use that as a way to do root cause analysis on what happened in those months the next metric that is good to track is the employee turnover rate which is basically the percentage of employees leaving the business it's a good metric to track because it lets you understand uh if you have a workforce that is slowly dwindling and also look for opportunities on how to improve your employee experience in order to improve retention rate which is the percentage of employees that are staying so the minimum requirements that you need to calculate e-turnover rates is the average head count in a certain period of time and the total levers both of which i've already aggregated here in our table and the formula to calculate the employee turnover rate is simply total levers divided by the average number of employees so in this case here you'll see in our table at the bottom we have some aggregations here uh gives you the total levers here um in this period of time which is a total of 60. that is fine and then we want to divide this by the average number of headcounts now this aggregation is not correct because it just adds up all the headcounts for each of those dates but what we can do is we can change the summarization of this to be average so now you will see that it gives us the average headcount for that period of time which is 492.67 so 60 divided by 492 60 492.67 multiply by 100 this is basically our employee turnover rate so now that we know how to calculate this let's create a measure to do exactly this so we're going to create a new measure here and i'm gonna name this one turnover rate and then we're gonna create a couple of variables to hold uh the numbers that we want so first we want to create something called uh total levers and then we're gonna do some of levers pretty simple next we're going to create one for average headcount which i'm going to do average headcount which will be the turnover table now that we have those two variables let's return it i'm gonna do divide once more levers and then average head count so let's have a look at this put this in a card and then change it to a percentage so here we go so 12.18 is the turnover which is exactly what we have in our calculator here the next metric that you should track is the absence rate so this metric is good because it allows you to track how many working days were lost from the different types of absences within your organization at the minimum you'll need this data in order to calculate the absence rate first you'll need the headcount which is the total number of active employees in your organization next you'll need the total available working days so here in our table we have working days here which shows us the total number of working days available for that month within that year and the third is the number of absence dates that were taken in that month so here in our table we've conveniently aggregated all of this data for you and the formula to calculate the absence rate is pretty simple it's just the number of absent states divided by the total employees multiplied by the working days so let's try to do that in action let's create a new measure for this we're going to name this one adsense rate but first we're going to create a new variable called absence days which will be average of the adsense days next we're going to create the average head count which will be average and yeah average head count here and didn't mean to press enter and lastly we'll need to create another one which is the average working days which will use average once more and uncertainties and working days right so now that we have all the variables that we need we'll do a return and then we'll create a divide so the if you remember the formula is pretty simple it will be absence days um divided by the employees which is the head count multiplied by working days so hit enter we drag that measure here on a card we'll change this into a percentage and it gives us 0.86 percent absence rates now one great thing about the fact that our table is already aggregated by uh dates is uh this measure that we created here changes based on the context so overall for the whole history that we have here is a 0.86 absence rate however if you drag the absence rates on this table here you actually are able to see how the absence rates are for different periods of time so you can see which months had the highest absence rates and use it the same way as you use the other ones as a means to analyze or find the reasons why you had such high absence rates for those periods so the fifth and final metric that i think you should track is the training completion rate so as an organization you want to ensure that your induction trainings are completed a low completion rate could pretty much impact the business especially if you have trainings that are mandatory or compliance training at minimum you will need employee level information so employees on an individual basis per row and then their training statuses so if they've completed their training if they have not started it or if they've started it but they've not finished it to calculate this one is actually pretty simple so we're just gonna go straight to it so we're gonna create a new measure here in in our table we're gonna name this one competition rate going to create a couple of variables once again we just want to hold some of our values so we're going to create this one completed i'm going to do calculate and we want to count training completion name matter and then for the filter we want training status is completed so that's the first variable and now we need to divide this by the total number of um people that needs training so we're just going to copy all of this well actually no we don't have to we're just gonna write another one so all and then we're just going to do a count of employees so that will be the total now we're going to return this and then we're going to divide so we want to divide completed by all so that will give us if i change this into a card and then i change the value to a percentage so here we go so it says to us that we have a completion rate of 31 uh for our total headcount so this number tells us that in within our whole organization only 31 of our employees have completed their training however with this calculation we also included employees that haven't started their training so there's a different variation for this metric that you sometimes want to see so let's say you want to know the condition rates for your trainings but only for those that have started it but not completed it you want to exclude who hasn't started the training at all because you're trying to find out maybe there's something in the training materials that needs to be improved and look for opportunities for improvement so we can do the same thing but just change it a little bit so we're going to do exactly that so we're going to go back to this measure here i'm just going to copy it just to save us some time i'm going to create a new measure we're going to name this one condition rates 2 and then we're going to create change the all slightly here so instead of counting all the employees we're going to add a filter to that so i'm just going to copy the completed variable and then here we're going to say give me everyone except the ones that says not started so that's it so that's the completion rates uh for those that have started it but hasn't finished it if we put that in a card here and then change it to a percentage so there you go so the these are your numbers uh at the moment so overall you have 31.9 completion rate for all the people that have completed it um and out of the people that have started the training and there's a completion rate of 50 percent which is relatively higher than uh the overall you can even take this one step further because we have this start date as sort of a date of when they started or when they started the training you can you know look for some trends of when they've completed or when the completion rates were at its highest or lowest so to do that we're going to do the same thing that we did with the offer acceptance rate which is to use the calendar table as our raw context filtering so we're going to go back to the model here i'm going to add the training completion rates and the calendar i'm gonna create a relationship between the two dates so the date here and the start date here and then now we can use the calendar as a filter so we're gonna drag the year month here and then we're going to give the completion rates so we can add this one we can add both so it gives you in this table not just the overall completion rate that we have here on the card so you'll see here at the bottom but also a breakdown of it um by month so you'll see okay so which month had the lowest completion rates and you know find some reasons why those numbers were there in the first place and that's really it for this video i know that there's a lot to take in and there were a lot of examples that we went through but i hope this was really useful for you to know what type of metrics are really important when it comes to analyzing you know hr data that will help your organization look for opportunities for growth or improvement thanks for watching as usual give this video a like if you found it useful give it a dislike if you didn't so not to do better for next time ask your questions in the comment section box below so i can help you and you can help others if you really like this video we have a patreon page where you can support the channel and get exclusive perks like early access demo files and credits at the end of these videos thanks again for watching and see you in the next one bye

Channel: Fernan

Views: 16,395

Keywords: solutions abroad, power bi, powerbi, power bi tutorials, power bi for beginners, beginners guide to power bi, data analytics, dax, data modelling, data visualisation, business intelligence, power bi 2021, how to power bi, power bi how to, power bi best practices, power bi tips and tricks, power bi standards, power bi patterns, power bi help, power bi tips, power bi hr, power bi headcount, power bi human resources, power bi absence, power bi attrition, power bi in hr

Id: 0u9yF7lKGW0

Length: 28min 23sec (1703 seconds)

Published: Mon Dec 06 2021