The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
I'm trying to configure a formula to calculate the 'Overage' Hours of a days work. From my data I did a 'GroupBy' within PowerQuery to condense all my columns to only the necessary fields to narrow down my calculation for easier functionality.
However, I'm stuck on this final piece on this calculation.
The goal is to be able to track how much time is worked past 8 hours (if you work 9 hours in 1 day, your overage hours would be 1 hour). In addition, a regular work week being 40 hours, I can exceed 40 hours and go into overtime within the same work day. I don't know how to manage this formula to sum 'Regular' hours and 'Overtime' hours together and subtract it from 8 to get the leftover hours worked.
My current formula is super simple as I keep trying to build on it, but don't know how to go about it.
=IF(SUM[Hours]-8<=0,"",SUM[Hours]-8)
Date Category Person Hours
01/20/2025 | Regular | A | 5.5 |
01/20/2025 | Overtime | A | 3.5 |
01/20/2025 | Regular | B | 5 |
01/20/2025 | Regular | B | 4 |
Person A 1/20/2025 = 1 Overage hour (but 3.5 hours of Overtime)
Person B 1/20/2025 = 1 Overage hour (No overtime accumulated yet)
This is over thousands of rows of variances of hours worked.
Apologies if it doesn't make much sense I can try to clarify more if asked. But I'm still fairly a novice at DAX and Power Query but would love some insight and advice on how to get this solved.
You could create a measure like
Overage hours =
SUMX (
VALUES ( 'Date'[Date] ),
VAR TotalHours =
CALCULATE ( SUM ( 'Table'[Hours] ), REMOVEFILTERS ( 'Table'[Category] ) )
VAR Result =
IF ( TotalHours > 8, TotalHours - 8 )
RETURN
Result
)
I should have also mentioned that within the category there are other categories aside from 'Regular' and 'Overtime'. I looked into KEEPFILTERS and FILTER but haven't been able to mimic your code to output data towards it. I do think this is a step in the right direction. Any suggestions?
If you only want to count the regular and overtime categories you could use
Overage hours =
SUMX (
VALUES ( 'Date'[Date] ),
VAR TotalHours =
CALCULATE (
SUM ( 'Table'[Hours] ),
'Table'[Category] IN { "Regular", "Overtime" }
)
VAR Result =
IF ( TotalHours > 8, TotalHours - 8 )
RETURN
Result
)
@Tummi To calculate the 'Overage' hours for each person on each day, you can use Power Query to transform your data and then apply a custom formula. Here's a step-by-step approach to achieve this:
Group By Date and Person:
Go to the Home tab.
Click on Group By.
Group by Date and Person.
Add a new column for the sum of Hours.
Go to the Add Column tab.
Click on Custom Column.
Use the following formula to calculate the overage hours:
if [Sum of Hours] > 8 then [Sum of Hours] - 8 else 0
Here is a more detailed example of the steps in Power Query:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
GroupedRows = Table.Group(Source, {"Date", "Person"}, {{"Total Hours", each List.Sum([Hours]), type number}}),
AddedCustom = Table.AddColumn(GroupedRows, "Overage Hours", each if [Total Hours] > 8 then [Total Hours] - 8 else 0),
RenamedColumns = Table.RenameColumns(AddedCustom,{{"Total Hours", "Sum of Hours"}})
in
RenamedColumns
Proud to be a Super User! |
|
Thanks for the help! The table I provided the example was already grouped to specific columns I needed. So I'm glad I'm getting closer to the results I'm needing.
User | Count |
---|---|
11 | |
8 | |
6 | |
6 | |
6 |
User | Count |
---|---|
23 | |
14 | |
13 | |
10 | |
8 |