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:   name date hours hour type Marcelo 01/05/...
  • 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 )
)
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.