Building an Employee Costs report with Power BI
And it seems that this time I did something that is not related to sports… :)
DISCLAIMER: First of all, it is important to emphasize that all the names and values you will see below are fictitious and were created solely and exclusively for this purpose. This is a fictional company and these people do not exist.
In the last few days I’ve been wanting to make a new Power BI report. This time I wanted to make something that was not related to sports, like those I have been doing so far.
I wanted to make a report with practical business applicability and that’s when I came up with the idea of doing something related to HR. The world of People Analytics has countless possibilities, however, something that is very common in companies is the cost analysis of the employees’ structure.
Having said that, I decided to do this type of analysis in Power BI (something that is often done in Excel) and this was what i came up with:
As you can see, there are several visuals in this report. I will guide you through each one of them.
Firstly, we have two Slicers that allow to choose the month and the team for which we want to see the information in the rest of the visuals.
Then, there are four Cards, each with a specific information, that can be filtered by team and by month.
Employee Yearly Cost (YC) — This card contains the amount of the employees’ annual cost. In the image above we can see the amount for all teams in April, since the “All” value in the “Team” slicer allows to see the value of the entire employee structure. Here, if we choose “iOS” in that same slicer, the card will only show the value for that specific team in that specific month. The images I will share next will show exactly that.
Number of employees — This one is pretty straightforward, it shows the number of employees according to the team and month chosen in the slicer.
YC % Diff: Beg. of the Year — Selected Month — This card allows to know the % difference between the value of the employee Yearly Cost (YC) at the beginning of the year (January) and the value of the YC in the month chosen on the slicer. If the % is positive, it means that there was an increase of “X” %, if it is negative it is because the value has decreased.
Average YC per Employee — This is also an easy one, it shows the average YC per employee.
Moving on to Gauge, the minimum value is the Employee Yearly Cost at the beginning of the year — You can confirm this by looking at the visual with the title “Yearly Cost and Number of Employees by Month”.
The target value and the maximum value were set according to the following reasoning: Imagine that you are the CEO of a company and that in the budget exercise at the beginning of the year, you decide that you want to grow the teams and that you’re willing to spend an additional 150k per year (which can go up to 200k) in employee costs. Thus, this Gauge (which cannot be filtered by team, as the values here are regarding the cost of the whole employee structure) allows the CEO to monitor every month how the employees’ costs are evolving. In terms of formatting, if the value is below 950k the bar is green, if it is above 950k and below 1M it is orange, and if it hits 1M the bar becomes red. See this example:
Now, let’s move on to the Table with the title “Detailed Employee Info”. This table, which can be filtered by month and by team (see the example below of the Android team in February) contains several columns with specific employee information. We choose “All” in the “Team” slicer to see the info for every employee of the company.
Regarding the line chart, it contains two lines, which allow to see the evolution of the Yearly Cost and Number of employees since the beginning of the year. This visual can only be filtered by the Team’s slicer, since every month’s info is already there. Here’s a more detailed look:
Finally, we have the Matrix. Here, we can see the Yearly Cost of each business area and each team. We can also see what is the weight % that the Yearly Cost of each business area / team has in the Total Yearly Cost. This visual can only be filtered by the Month’s slicer. Here’s a closer look:
And this is it! I didn’t go into much technical detail in this post (although I had to do some work with DAX to pull this off), however, and if anyone wants, I can share how I’ve made the visual “X” or “Y“ :)
Feel free to give suggestions and ideas on how to improve this!
Cheers,
André
