Calculating Over Time

[navarrobr]

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.

[v-haibl-msft · Accepted solution]

@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

[v-haibl-msft · Accepted solution]

@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

[navarrobr · Accepted solution]

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

[ankitpatira]

@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.