Employee Analytics

I was a part of OUIBootcamp 30 days of learning in the data analysis track and at the end we undertook a capstone project. In this project I assume the role of an analyst to provide a company with gender demographic insights for their workforce. I use Excel and Power Query to clean and transform the data and visualize with PowerBI by the guidelines provided by the client.

0.1 Client guidelines

Data Cleaning

For this project I was provided two workbooks in .csv format about employee data and their bonus percentages in another workbook. I loaded the first table with employee data into power query:

1.1 Sample dirty data

I examined this data and found that some values were missing and there were some duplicate values. And a quick overview of the questions made it clear that I needed to add some calculated columns and create some measures. For example, the second workbook contained bonus percentages based on the employees' rating. To have the percentages in the table, I loaded the employee bonus workbook in Power Query as a table and performed a Right Join with the employee data table to merge them. From that I was able to load the clean data into PowerBI and create a DAX Measure to calculate the Final total salary bonus inclusive. Other measures were created for my analysis. Missing values appeared only in the gender column. I fixed that by filling the empty records with "Other". If it was a numerical column, I would've have filled missing values with the mean or median of the entire column based on the distribution of the values.

1.2 sample of cleaned data in Power BI

Data limitations

To adequately conclude if there is a gender pay gap there would need to be a comparison between job roles and levels in the company which the datasets do not provide.

Insights and visualization

The client required that I provide insights on specific questions mostly surrounding gender demographics and inequality as seen below

2.1 case questions

I set out to answer all these questions and visualize them into an interactive dashboard that stakeholders could interact with to get very specific data if needed

2.2 Interactive PowerBI Dashboard

Visit this link to get the full interactive experience

Conclusion

This was a good exercise of people analytics and it was fun sifting through data to arrive at actionable conclusions. Also, PowerBI is an amazing visualization tool but that is not all it is good for. If you know your way around DAX, it makes it a powerful data transformation tool. Thanks for reading and as always, reach out for any questions or suggestions. I am always

open to learning more