SEGRO Courses

Home | Excel | Power BI | Copilot | Visualising Data

Power BI HR Course Notes

This contains useful information for attendees of the HR Power BI course.

Data Links

During the course, attendees will need these links.

• Employee Data
• HR Lists
• Absence Data
• Dates Table

Copilot Prompts

We may also ask Copilot help us with some calculations. If so, sample prompts are as follows

In Power BI, I have a table Employee with a [Leave Date] column. Write the DAX to create a calculated column named IsCurrentDax with values of Current if [Leave Date] is empty, otherwise Former.

The Employee table has a column [Birth Date]. Write the DAX for an Age column.

Data Description

This data is fictional data. The data values have no resemblance to any actual data. It was create by a script that used a random process to generate the values.

Employee Dataset

The employee dataset contains details of 1,000 people born between 1960 and 2004. On some date, randomly assigned between their 21st birthday and the current date, each of these joins SEGRO. At some later date, some of these have left - there are about 550 current employees. Employees have the following attributes.

• Employee Id, a unique identifier for the employee
• Gender
• First name
• Last Name
• Status - either growth or permanent role
• Vacancy Type - either Replacement or New
• Grade Key - a number that links to a corresponding identifier column on the Grade table in the hr-lists.xlsx spreadsheet.
• Department Key - a number that links to a corresponding identifier column on the Department table in the hr-lists.xlsx spreadsheet.
• Workforce Planning A Key - a number that links to a corresponding identifier column on the Workforce Planning A table in the hr-lists.xlsx spreadsheet.
• FTE - the value is either 1 (for a full time employee) or 0.5 (for employee who work 50% part time)
• Birth Date - date of birth
• Join Date - date that the employee joined the company
• Leave Date - date that the employee left the company, or blank for current employees. Note that some employee have leave dates a few months into the future. The employee are working out their notice.
• Reason for Leaving - values exists only for those employees who have left the company.
• Rating How - has values Exceptional, High, On Track. Ratings were introduce in 2024 so employeeS who left the company before then do not have a rating.
• Rating What - as above
• Absence Rate - this is internal data which we will not use in reports

There are (clearly) some unrealistic features of the employee dataset. For example, employees have first and last names drawn from a typical sample found in the UK. The data generation script randomly combines first and last name to generate a full name. Attributes like gender and grade have been assigned in a random fashion so that there is no correlation between for example, grade level and tenure. However for the purpose of generating some sample Power BI reports, this hardly matters.

HR Lists

This Excel spreadsheet contains small lookup lists with details of categories used in the reports such as:

• Grade
• Department
• Workforce Planning A

Each of the tables has a ‘key’ column, with unique values that match the values in the corresponding ‘key’ columns of the employee dataset.

Each list is defined as an Excel table, a named rectangular range with data organised in tabular format and with column headers. Power BI can read directly from an Excel table.

Absence Dataset

The absence dataset has a row for each date that an employee was absent from work. The columns are:

• EmployeeId
• Date
• Absence Reason

The script randomly assigned each employee a absence rate between 0% and 10% and then created random absence dates based on that absence rate for the period between join date and leave date (or today if they are still employed). For each absence date, the script randomly assigned an absence reason. Obviously this is unrealistic - it does not reproduce the episodic nature of absence patterns especially for some absence reasons.