BYU Student Author: @Jacob_Dutton
Reviewers: @James_Gerstner @kyle_Nilsen
Estimated Time to Solve: 30 Minutes
We provide the solution to this challenge using:
- Power BI
Need a program? Click here.
Overview
Congrats! You have just been hired as a financial analyst. You have been provided 3 CSV files and a task to audit the company’s payroll data. Use calculated columns in Power BI to determine if any employees have been receiving extra pay, and if any employees have been paid after termination. There are several ways to go about this challenge, pick whichever you believe to be most efficient.
Instructions
- Load the data into Power BI.
- Create a 1:1 relationship between Employee_Hours and Payroll_Export. Do this by creating a calculated column that combines Employee ID and Period End in each table, and then creating a relationship in the model view.
- Create a 1:Many relationship between Employee_Data and Payroll_Export on the field Employee ID.
- Find out if any employees have been overpaid. Assume that each has 15% of their gross pay withheld for taxes.
- Find if any employee has been paid after their termination date.
- You do not need to create any visualizations for this challenge, you are only providing management with the information they need. Your calculations and solutions will likely be contained in the table view in Power BI.
Data Files
Challenge218_Employee_Data.csv
Challenge218_Payroll_Export.csv
Challenge218_Employee_Hours_csv
Suggestions and Hints
Hints:
If you are having trouble with joining Employee_Hours and Payroll_Export using the calculated columns, you can also perform this step in power query editor.
Solution
Answers
- No employees were overpaid.
- Employee 4 was paid after termination.
Challenge218_Solution.pbix
Solution Video: Challenge 218|POWERBI – Payroll Pain