cancel
Showing results for
Did you mean:
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:

 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?

2 ACCEPTED SOLUTIONS

Accepted Solutions
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 )
)```

Best Regards,

Herbert

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

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

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.

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

Best Regards,

Herbert

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