In this tutorial, I will show you how to build this simple HR Power BI report in less than 10 minutes. For this project, we need Power BI Desktop and basic HR related data such as: Employee ID, Employment and Termination Date, Function, Department, Education, Gender, Job Type and Work Type, Employment Status and Region. This report is made of 2 highly interactive pages with responsive visuals that allows the end user to slice and dice the data according to their needs.
We are going to:
- Get Data from Excel and Transform
- Create a simple measure
- Plan the KPIs and report pages
- Create visuals such as: stacked bar charts, stacked column charts, donut charts, cards and slicers
- Publish in the Power BI Service
Step 1: Get and Transform Data
For this report we will get data from Excel. In Power BI Desktop we will click on the Get data button, choose Excel, navigate to the file location and click on Open. This will open the Navigator window where we can preview the data. In this case we see the name of the table “HR_Table” and the name of the Sheet “fHR”. We select the “HR_Table” and click on Transform Data. In the Power Query Editor we can see how many rows and columns the table has.
Power Query is particularly good at detecting and automatically changing the data types of each column as they are imported from the data source. However, we should change the data type of the Employee ID column because that column is the basis of our main measure: Total Employees. We can easily change the data type by clicking on the column name and navigating to Home – Transform – Data Type and selecting Whole Number. The transformation gets registered in the APPLIED STEPS pane to the right. The only thing left here is to click on Close & Apply and the data will be loaded.
Step 2: Create measures
After loading the data in Power BI, the next step is to create measures. In our case we are interested to know what the total number of employees is and the way to find this out is to create a new measure. A best practice here is to create a new table which we will call “Key measures”. Go to Home, click on Enter Data, type in the name of the new table and click Load.
To create a new measure, we need to right click on the name of the new table “Key measures” and click on New measure. To find out the number of total employees we can use a simple DAX function that counts the number of rows in the HR data table. In the formula bar we can type in the following expression:
Total Employees = COUNTROWS(HR_Table)
Step 3: Plan the KPI’s
There are a multitude of visualizations in Power BI to help us tell our data story. In our case, we have 8 columns with categorical data and 2 Date columns. The main measure as we’ve seen will be the Total Employees.
We can create 2 report pages, one in which we show proportions of categorical data by using donut charts, stacked column charts and stacked bar charts related to Total Employees. The second page will be focused on the Dates columns where we can leverage the stacked column chart to provide more insights into the changes in a series of data and where they occurred. Here, we can leverage slicers to allow the user to specify the desired level of granularity.
A third page can act as our Tool tip page that will provide even more insights into specific metrics.
The report will contain visuals for the following:
- Total Employees by Education
- Total Employees by Function
- Total Employees by Gender
- Total Employees by Job Type
- Total Employees by Work Type
- Total Employees by Department
- Total Employees by Region
- Total Employees by Year and Month
Another important consideration is the visual aspect of the report. The Theme gallery can be accessed under the View tab. For this report I will use the Bloom Theme and I will only make a minor adjustment to Border of the Visuals by setting the radius to 30 px.
Step 4: Create Visuals
Page 1: Total Employees
For the first page we can place up to 8 visuals. I will create 5 Donut charts for the categorical data with up to 3 categories. For the Department I will create a stacked bar chart and for a Shape map for the Region data.
To speed up the process I will start by inserting one donut chart where I drag the Total Employees into Values and Education into Legend. I format the donut chart to 400 Width and 200 Height. Then I copy and paste this visual 4 times. We can place two to the left and three to the right of the page. In the center we can create the stacked bar chart and the shape map. The last visual is the card for the Total Employees. Optionally we can create a filter to focus on the Active employees.
Page 2: Employment and Termination
On this page we can focus on comparisons over time and the best visual for this is the stacked column chart. We can use the two Date columns for Employment and Termination to distribute the Total Employees by Month in a selected year. Then we can use the other categories as slicers to drill down to the wanted insight. We have to simply click on the stacked column chart and drag the Employment Date into the Axis and Total Employees into Values. To insert the filter, we have to expand the Filters pane and apply Basic filtering under Filter type. We copy and paste the chart and change the Employment Date with Termination Date into the Axis.
Now we can select both charts and duplicate them. Under Visualizations we choose Card and place them to the left of the stacked column charts. The last step is to create some slicers. Simply insert a slicer and drag the desired column into the Field. I created 5 slicers for Work Type, Job Type, Function, Gender and Education.
Page 3: Tool tip
Tooltips are an elegant way of providing more contextual information and detail to data points on a visual. To simplify we can copy some of the visuals from the first page such as the Total Employees card, the Total Employees by Department stacked bar chart and the two donut charts for Total Employees by Job Type and Work Type.
We need to change the page size to be smaller than the main report pages. We do this by clicking on the Page size and changing the Width to 800 px and Height to 450 px.
The last step is to activate the Tooltip under Page Information and clicking on Hide Page.
Then we can add the tooltips for the Shape map visual on the Active Employees page by activating the Tooltip and pointing it to the Tool tip page.
On the Hires and Terminations page we can add the tool tip to the two stacked column charts. When the end user selects a slicer or clicks/hover on one of the columns, the tooltip will be visible. What is most impressive is that the tooltip automatically updates to show the selected data.
Step 5: Publish to the Web
The last step is to click on Publish, select a workspace, and publish to the web.