Power BI - Grouping and Running Calculation based on a date filter.

[datalalor]

I need some help with grouping data and running calculations with date filters. Here is the original data, and I would like to add some of the calculation of the gross pay (Reg Pay+Bonus Pay), total hours (Reg Hours+OT Hours), and Actual Hourly Rate (gross pay / total hours).

First Name	Check Date	Reg Pay	Bonus Pay	Reg Hour	OT Hours
A	12/1/2023	175		2	1
A	12/7/2023	225	500	3	1
A	12/14/2023	175		2	1
B	12/1/2023	175		2	1
B	12/7/2023	275	100	4	1
B	12/14/2023	375		5	1

What I need is, if I set the date range from 12/1/2023 to 12/14/2023, it will group first name, sum Reg Pay, Bonus Pay, Reg Hour, and OT Hours, then do the calculation of gross pay and total hours, then run the calculation for the actual hourly rate.

I added Gross Pay, Total Hours and Actual Hourly Rate as a new column, please see below.

Gross Pay = Payroll_Journal[Reg Pay]+Payroll_Journal[OT Pay]

Total Hours = Payroll_Journal[Reg Hours]+Payroll_Journal[OT Hours]

Actual Hourly Rate = IFERROR(Payroll_Journal[Gross Pay]/Payroll_Journal[Total Hours],0)

However, this is my return result, which is returning the sum from each paycheck's actual hourly rate.

First Name	Reg Pay	Bonus Pay	Reg Hour	OT Hours	Gross Pay	Total Hours	Actual Hourly Rate
A	575	500	7	3	1075	10	297.92
B	825	100	11	3	725	14	162.5

I am hoping to get the following result, is it possible?

First Name	Reg Pay	Bonus Pay	Reg Hour	OT Hours	Gross Pay	Total Hours	Actual Hourly Rate
A	575	500	7	3	1075	10	107.50
B	825	100	11	3	725	14	51.79

[datalalor · Accepted solution]

This solved! Turns out I need to set the Actual Hourly Rate calculation as a measurement instead of a column. 🙂