Design a Power BI dashboard tailored to address the challenges faced by HR professionals

Problem Statement

The HR (Human Resource) department is the heart of every organization. From

recruitment to compensation to performance appraisal and employee wellbeing, the

HR department plays several roles in the enterprise. HR acts as a mediator or a

bridge between the employees and the management or enterprise. It’s no surprise

that the HR department is already burdened with work. Providing them access to the

latest technology and the means to derive insights in real time will help reduce the

workload and create a healthy environment throughout the organization

Market fluctuations and rapidly changing technology have affected the global market.

Many published reports showed that around half of the employees wanted to change

jobs. While some market researchers said that flexible working and job security were

their primary factors, few admitted that a higher salary was their aim.

Different regions saw an increase and a decrease in salaries over the years. While the

increase was to retain top-level professional employees, the pay cuts were due to

market fluctuations and were resorted after the market conditions improved. HR

people across the globe are hiring new employees, trying to retain and understand

the needs of employees who got separated (those who left the company).

So, how does the HR department make these decisions in volatile market conditions?

They rely on HR analytics to understand the existing situation and develop a new

modern approach. For this requirement, you have been asked in your company to

build a dashboard in Power BI considering the following challenges of HR people and

provide an effective way to find the answers to their day-to-day questions.

Tasks

• Use the HR data set provided for this project and analyze that to understand

• the data and terms.

• Load data into the Power BI Query Editor and perform the required actions.

• Establish the required relationships (look at the hint section).Create the required DAX columns and measures to calculate the below things (look at the hint section).

Create the below-calculated column in the BU table: Region = mid([RegionSeq], 3,15)

Create the below columns in the Employee table:

• AgeGroupID = IF([Age]<30, 1, IF([Age]<50, 2, 3))

• isNewHire = IF(YEAR([date]) = YEAR([HireDate]) && MONTH([date])=MONTH([HireDate]), 1)

• TenureDays = IF([date]-[HireDate]<0,[HireDate]- [date],[date]-[HireDate])

Create a new measure table (separate table using Enter data) and place all the measures into this table.

a. Create a new Measure EmpCount

Hint: EmpCount = CALCULATE(COUNT(Employee[EmplID]), FILTER(ALL('Date'[PeriodNumber]),'Date'[PeriodNumber] = MAX('Date'[PeriodNumber])))

b. Create a Measure for Active Employees

Hint: Actives = CALCULATE([EmpCount],

FILTER(Employee, ISBLANK(Employee[TermDate])))

//total active employees

c. Create a Measure called New Hires, which will have the sum of the new NewHire in the Employee table.

d. Create a Measure called Separations.

CALCULATE(COUNT(Employee[EmplID]), FILTER(Employee,

NOT(ISBLANK(Employee[TermDate]))))

//separations/employees who left

e. Create a Measure called AVG Tenure Days which has an average column

TenureDays from Employee table.

f. Create a Measure called AVG Tenure Months.

Hint: AVG Tenure Months = ROUND([AVG Tenure

Days]/30, 1)-1

g. Create a Measure called Female Emp Actives.

Hint: Female Emp Actives =

CALCULATE([Actives],Gender[Gender]=”Female”)

h. Create a Measure called Female New Hires using New Hires.

i. Create a Measure called Female Separationss

Hint: Female Separationss =

CALCULATE([Separations],Gender[Gender]="Female")

j. Create a Measure called Male Actives

Hint: Male Actives =

CALCULATE([Actives],Gender[Gender]="Male")

//how many Active male employees

k. Create a Measure called Male New Hires

Hint: Male New Hires = CALCULATE([New

Hires],Gender[Gender]="Male")

//how many new hire male employees

l. Create a Measure called Male Separations

Hint: Male Separations =

CALCULATE([Separations],Gender[Gender]="Male")

// male employees who left or separations

1. Based on the below design, create the report in Power BI.

2. Implement row-level security based on the BU region.

3. After developing this, save your file as “FinalProject_YourName.pbix”.

4. Publish your report in my workspace using the demo PBI service account

5. (provided during training) or your own account.

6. Create the dashboard in the Power BI workspace using the key visuals; identify

7. the visuals that show key information and pin them on the dashboard.

8. Export the report to PowerPoint and the dashboard to a PDF file.

9. Submit the PowerPoint report, PDF dashboard, and .pbix file.

Sample Screenshots:

If you need implementation for the above problem or any of its variants, feel free to contact us.