cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
navarrobr Frequent Visitor
Frequent Visitor

Calculating Over Time

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:

 

namedatehourshour type
Marcelo01/05/20168available
Marcelo01/05/20169worked
Marcelo02/05/20168available
Marcelo02/05/20165worked
Marcelo03/05/20168available
Marcelo03/05/20169worked

 

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:

 

namedateAvailable Hours
Worked Hours
Marcelo01/05/201689
Marcelo02/05/201685
Marcelo03/05/20168

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:

 

namedateAvailable Hours
Worked HoursOvertime
Marcelo01/05/2016891
Marcelo02/05/2016850
Marcelo03/05/2016891
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.

2 ACCEPTED SOLUTIONS

Accepted Solutions
v-haibl-msft Super Contributor
Super Contributor

Re: Calculating Over Time

@navarrobr

 

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 )
)

Calculating Over Time_1.jpg

 

Best Regards,

Herbert

 

navarrobr Frequent Visitor
Frequent Visitor

Re: Calculating Over Time

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 )
)
3 REPLIES 3
Super User
Super User

Re: Calculating Over Time

@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

 

Capture.PNG

 

 

 

 

 

 

 

 

 

 

 

 

 

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.

 

Capture.PNG

v-haibl-msft Super Contributor
Super Contributor

Re: Calculating Over Time

@navarrobr

 

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 )
)

Calculating Over Time_1.jpg

 

Best Regards,

Herbert

 

navarrobr Frequent Visitor
Frequent Visitor

Re: Calculating Over Time

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 )
)