Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello. I was wondering if someone could help me with the following situation:
Imagine that I have a table of hours for employees, like this:
name | date | hours | hour type |
Marcelo | 01/05/2016 | 8 | available |
Marcelo | 01/05/2016 | 9 | worked |
Marcelo | 02/05/2016 | 8 | available |
Marcelo | 02/05/2016 | 5 | worked |
Marcelo | 03/05/2016 | 8 | available |
Marcelo | 03/05/2016 | 9 | worked |
I would like to calculate the total overtime - that would be the difference between the worked hours minus the available hours (but only when the worked hours are higher).
I created two measures, in order to see the available and worked hours "side by side":
Available Hours = calculate(sum(Tabela[Hours]);Hours[hour type]="available")
Worked Hours = calculate(sum(Tabela[Hours]);Hours[hour type]="worked")
Then, I could have a pivot table like this:
name | date | Available Hours | Worked Hours |
Marcelo | 01/05/2016 | 8 | 9 |
Marcelo | 02/05/2016 | 8 | 5 |
Marcelo | 03/05/2016 | 8 | 9 |
TOTAL | 24 | 23 |
Then I created another measure, trying to calculate the overtime:
Over Time = if([Worked Hours]>[Available Hours];[Worked Hours]-[Available Hours];0)
The result was:
name | date | Available Hours | Worked Hours | Overtime |
Marcelo | 01/05/2016 | 8 | 9 | 1 |
Marcelo | 02/05/2016 | 8 | 5 | 0 |
Marcelo | 03/05/2016 | 8 | 9 | 1 |
TOTAL | 24 | 23 | 0 |
You can notice that the overtime is being correctly calculated for each day, but in the total level the result should be 2 instead of 0. How should I build my measure to correctly calculate the total overtime?
Sorry for my bad english.
Solved! Go to Solution.
Please try to update the measure of Over Time like below.
Over Time =
SUMX (
VALUES ( Tabela[date] ),
IF ( [Worked Hours] > [Available Hours], [Worked Hours] - [Available Hours], 0 )
)
Best Regards,
Herbert
Thank you, I used your insight to find a better solution:
Over Time =
SUMX (
SUMMARIZE( Tabela[date], Tabela[nome] ),
IF ( [Worked Hours] > [Available Hours], [Worked Hours] - [Available Hours], 0 )
)
Please try to update the measure of Over Time like below.
Over Time =
SUMX (
VALUES ( Tabela[date] ),
IF ( [Worked Hours] > [Available Hours], [Worked Hours] - [Available Hours], 0 )
)
Best Regards,
Herbert
Thank you, I used your insight to find a better solution:
Over Time =
SUMX (
SUMMARIZE( Tabela[date], Tabela[nome] ),
IF ( [Worked Hours] > [Available Hours], [Worked Hours] - [Available Hours], 0 )
)
@navarrobr what i would do instead is after importing table,
go to power bi desktop query editor
select hours type and hours table and under Transform tab click Pivot Column
pivot it by hours column
Close & Apply
Under modelling tab create new calculated column as below.
=IF( TABLE[worked] > TABLE[available], TABLE[worked] - TABLE[available], 0)
which will give you table as below. then use matrix or table visual to visualise where you can get Totals.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
101 | |
78 | |
75 | |
49 |
User | Count |
---|---|
145 | |
108 | |
107 | |
89 | |
61 |