🔴 LIVE: Build HR Analytics Dashboard from Scratch! - (February 13, 2021)

Join me live as I transform a very flat hr excel file into a Power BI analytics dashboard!
In this session we'll cover how to create simple measure like Headcount, Starters, Leavers, Turnover, and also other metrics that might be useful, based on what data you have.
🔻 Download the Power BI file (pbix) and CSV source file we used, right here!
ko-fi.com/s/c663b19bf7
-
☕ Show your support if you enjoy my content, a cup of coffee will do!
ko-fi.com/solutionsabroad
MY FAVOURITE POWER BI BOOKS
📕 The Definitive Guide to DAX - amzn.to/3ruYs2f
📗 DAX Patterns - amzn.to/3oVgOYs
MY GEAR
🎥 Canon 4000D DSLR Camera - amzn.to/3tCqDOw
💻 Cyberpower Wyvern PC - amzn.to/3q04QOw
🪑 Infamous Gaming Chair - amzn.to/3q3iUXw
💡 LED Studio Lighting - amzn.to/3q3ftQz
🎙 Lavalier Microphone - amzn.to/3jwKutG
📺 Monitor - amzn.to/2LuHhhO
GET IN TOUCH
📺 Website - www.solutionsabroad.co.uk
📧 Email me - [e-mail]
🤵 LinkedIn - / solutionsabroad
📘 Facebook - / solutionsabroad
📸 Instagram - / solutions_abroad
🐤 Twitter - / abroadsolutions
🤖 Reddit - / fernanespejo
🌍 WHO IS SOLUTIONS ABROAD
Hi, my name is Fernan, the one-man band of Solutions Abroad. I work as a Data Scientist based in London, UK and have over 6 years of experience working in Business Intelligence. Solutions Abroad these days is dedicated to teaching the ins and outs of Power BI, through the power of KZbin.
If you want to learn more, I’ve conveniently created playlists to help you find the right topic for you.
🐎 First, watch this video about how a typical Power BI workflow looks like
• Power BI from START to...
🗺 Learn DAX to extend your Power BI calculations
• Power BI DAX Basics OLD
📆 Stay up-to-date on all the features Power BI releases monthly
• Power BI Monthly Featu...
🧪 Get certified as a Microsoft Data Analyst Associate
• Power BI DA 100 Prepar...
💡 Learn Power BI Features to improve your dashboards
• Power BI Feature Highl...
📊 WHAT IS POWER BI?
Power BI is a business analytics tool by Microsoft. It creates a working environment suited for normal users, meaning tasks such as data extraction, cleansing, analysis and storytelling; these are made easily-accessible to citizen-users, and extendable for technical users. At face value it provides visually-stunning, interactive dashboards, but it’s built on top of so much more.
🔻 Get started with Power BI Desktop for FREE
powerbi.microsoft.com/en-us/d...
#PowerBI #DataAnalytics #BusinessIntelligence

[@user-wt2px2tt5p]

I find this webinar very useful for me to understand the way power bi is used for HR. I’m designing an HR dashboard framework and this presentation gave ideas how to work on that. My Best Wishes.

[@SolutionsAbroad]

Great to hear that, I hope it helped you, good luck on the presentation!

[@islamaly6535]

Very informative comprehensive and helpful tutorial, keep it up

[@jr713121]

Super helpful video. Thanks for sharing!

[@mohamedabdeen4226]

Keep going, your explanation is easier for starter because you are put your suggestions and proposal on live and the good that even correct thinks on spot and that make things much better

[@SolutionsAbroad]

Thank you!!

[@mohamedabdeen4226]

@@SolutionsAbroad need more workshops related to hr dashboard and also with different calculations especially for turnover, termination analysis, hiring, salaries, absence, attritions

[@aleksanderstrommer7677]

Just in time. I need this next week, thanks so much!

[@SolutionsAbroad]

You're welcome Aleksander, thanks for watching my videos!

[@aleksanderstrommer7677]

@@SolutionsAbroad 45:30 I scrached my head with my own project in the past. It turned out that quick calculation required (at least back then) auto date/time table which was was turned off from the settings and I was using my own date table which was marked as date table. I wonder is here same issue ?

[@aleksanderstrommer7677]

@@SolutionsAbroad I really like this format, it is great to see thought process and how to deal with challenges.

[@SolutionsAbroad]

@@aleksanderstrommer7677 Yes this also stumped me during the stream, I'm surprised you caught it! I have my own date table marked, so maybe I will give this one another shot, maybe I will end up just writing my own measure for this one

[@A96000]

What a legend! Thank you for sharing this

[@pallavijain3957]

Hi Fernan...Great video, Thank you for sharing. I want to ask...why we didn't use userelationship function in other calculations, although we were using both hire date and end date?

[@ixtension]

Hi Fernan, i like your videos and your authentic, sympathetic manner. thank you for your content! Can you please mention again the website for creating the raw data? I couldn't find it unfortunately. Many greetings from Germany and many thanks in advance, Markus

[@jyabuki0314]

Thanks for posting the video... I was tasked to create a KPI dashboard using Power BI at my job which got me exploring how I can utilize Power BI in different parts of my job. Anyways, I'm only 5 minutes into your video so might have more questions later, but what was your thought process in importing the Excel file as csv rather than xlsx?

[@SolutionsAbroad]

Let me know if you have any questions! The source doesn't matter, CSV or XLSX type works fine, it just so happened that my source is CSV this time round

[@namirakamal7097]

Hi, when calculating leavers, I notice that you add +0 to the measure to account for the dates with no leavers. When I attempt this it alters the actual number of leavers and starters. I also tried accounting for starters on an empty date (by +0) but was unable to as it changed the data again. Any suggestions for this?

[@megha3000]

Hi Fernan, This one is pretty much detail-oriented. Thank you. In my employee table; There was no end date, Can I add it to the loading time? Or add a column from the power query. Which will be better?

[@SolutionsAbroad]

Hiya, either will work, if adding columns to tables I prefer it to be on Power Query

[@user-of2zv1gj2p]

Thanks for this webinar sir Fernan.. May I ask if you are from the Philippines? When is your next live?

[@RafaelTT]

Hi Mate, thanks for sharing this. Really usefull, have you found out which was the issue with running total, I am really interested in it. As for you, it didn't work for me... :)

[@SolutionsAbroad]

Hey Rafael! This livestream feels like a lifetime ago! I wrote my own one that works, using the same setup, see below: CALCULATE( [Starters], FILTER( ALLSELECTED('Calendar'[Date]), 'Calendar'[Date]

[@kervinlau5266]

@@SolutionsAbroad Thanks for sharing. The DAX measurement based on starters gave me numbers that were off. Do you have one for headcount? The built-in quick measure did give me an accurate running total for headcount but the result is shown as a single value vs. values over time (date table).

[@riazuddin7493]

Hi Fernan can you tell me which key you use to write OR (II) in DAX?

[@SolutionsAbroad]

it's shift + \ key in the UK keyboard Riaz, the symbol is called a pipe symbol

[@svksrjlpriyankabusinessana5615]

Hi, Thanks for posting the video. How to calculate the Attrition % mom = if its April the formula has to be (Cumulative voluntary exits/Average Headcount)/1*12)

[@svksrjlpriyankabusinessana5615]

Could you help me with the dax formula

[@jadekennedy3342]

Hi there! My headcount measure doesn’t seem to be working properly. I have my data and measure set up the same way as you but unfortunately some people are being added to the wrong year! It also looks as if the year 2022 isn’t correct. Do you have any common errors that might be causing this?

[@pranshuagarwal7]

It did be better if you provide more details about the error. It can be due to DAX code or the data modelling part too.

[@riazuddin7493]

Hi Fernan this is Riaz again. I have a column in my table name reason for leaving and based on that I want to add another column which is Volunteer attrition or Involunteer attrition i.e. if reason for leaving for an employee appears as Retired then the new column should show Involunteer attrition whereas for resignation it should show as volunteer attrition. Can you help me add this column please? Also how can I calculate the percentage of volunteer and involunteer attrition based on the total attrition for the whole year which is 1157.

[@SolutionsAbroad]

Hi Riaz, let's break your question down again: - For new columns like that, I would suggest creating a conditional column in Power Query, because you get a UI that's a lot easier to use - For attrition you can follow my video which I think is similar to what you're looking for. If want attrition on volunteers, you need to put a filter context to it, see how I do it in this video kzbin.info/www/bejne/iaOWgJd7Ys-rlZo Good luck!

[@riazuddin7493]

@@SolutionsAbroad Hi Fernan can you please let me know the DAX for counting total staff number at on particular point in time. I have employee data in which date of joining is since 1974 to 2021 is there any way if I select 2019 then the total staff headcount show total staff who were present in 2019 whether or not they joined in 2019 in other words a cumulative total of staff whose date of joining is 2019 and backwards?

[@hiteshdangodra6440]

How to calculate moving average or running average of head count for each month

[@SolutionsAbroad]

Hi Hitesh, great question! there are different DAX functions you can use to calculate running average or moving average but the easiest way you can do it is through quick measures. I cover it in this video kzbin.info/www/bejne/iXeshJp6ityopaM

[@riazuddin7493]

How do you jump to next number while writing DAX I tried by hitting enter but it didn't work because enter is to close your DAX command. I believe there must be other key to go to next number

[@SolutionsAbroad]

Hey Riaz, great question, just Alt + Enter

[@riazuddin7493]

@@SolutionsAbroad Hi Fernan thank you for your reply need your help! I have two tables Active and Resigned and one central date table. I have successfully created visuals for active i.e. headcount by function, grade, region and type but while creating visuals for resigned like year on year leavers I get visuals for all those staff who were not even there in that year. The active table has date of joining since 1960 whereas the resigned table has only three years data 2019, 2020 and 2021 based on the last working date so the power bi should only give my data related to these years only please note that I have calculated total leavers through quick measure based on the last working date and running total on the same pattern and I am using Year (date table) on my axis and running total on value but it gives me the details since 1960 which is from the Active table but there is no last working date which is on the resigned table so the Power BI should only read values from resigned table and bring data for these three years only. Can you please help me fix this?

[@SolutionsAbroad]

@@riazuddin7493 Hey Riaz, very lengthy scenario you have here! Let's see if I can break down it down for you: - Have you created a relationship between your Active employees and resigned employees? - If you wanted to your leavers only for the last 3 years, filter the BLANKS from your measure, that should do the trick The last thing you could try to do is exclude the BLANKS from your measure, I think I do something similar here in the stream with the headcount calculation, copy that and see if ti works. Good luck!

[@nawafalsubeaei7803]

thank you so much but please help! i did everything but the date slicer does not work. pls pls pls advise.

[@SolutionsAbroad]

Hey Nawaf, can you check if you have created a relationship between the calendar table and the employees table? This is usually the cause when the slicers don't work as expected

[@mohammedaltaf4877]

V Good

[@riazuddin7493]

How to create a date table when I have a date column with repeated values, I tried Calendarauto but received the error message "A table of multiple values was supplied where a single value was expected". I have date of joining in my data where more than one people joined on a particular day

[@SolutionsAbroad]

Sounds like you're trying to create a scalar measure. You need to create a New Table, not new measure, when using CalendarAuto

[@riazuddin7493]

@@SolutionsAbroad thanks this worked perfectly and I have created a date table

[@riazuddin7493]

How can I calculate running total of employees based on their joining date so that on my visual only number of employees who joined in a year shows I have calculated total headcount based on count rows but in visual for every year it shows total headcount of 14702 which is the overall headcount

[@riazuddin7493]

Oh.. I have calculated it through userelationship after watching your video Thanks for your help

[@SolutionsAbroad]

@@riazuddin7493 you're welcome!

[@gowthamigg8976]

hi Fernan, headcount i am getting blank what to do

[@olaola-hv4zw]

how do i calculate retention rate for the hr department?

[@SolutionsAbroad]

Hiya, there are many ways from my experience to calculate retention rate. An example would be to divide the number of people employed during a period / number of people employed at the start of the period.

[@Daadhiiwala]

Year column is showing blank against headcounts

[@imranpathan8352]

hi..nice video..please provide csv file and pbix file

[@SolutionsAbroad]

Download files in the description box, enjoy!

[@sathyanarayana738]

i need this dataset plaese share to me this link

[@niaaaawtf]

reminder for me 54:32

[@ghadamusaad2139]

Thank u so much for this video it’s very useful. Please I need your advice here 🥹, if i want to calculate Monthly Turnover Rate per two months.. Ex. = (January levers + February levers) / Average (January C + February HC) Please advise on Dax