Forum Discussion

navarrobr's avatar
navarrobr
Frequent Visitor
10 years ago
Solved

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.

  • v-haibl-msft's avatar
    v-haibl-msft
    10 years ago

    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's avatar
    navarrobr
    10 years ago

    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

  • v-haibl-msft's avatar
    v-haibl-msft
    Microsoft Employee
    10 years ago

    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's avatar
      navarrobr
      Frequent Visitor
      10 years ago

      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's avatar
    ankitpatira
    Community Champion
    10 years ago

    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.