Forum Discussion

navarrobr's avatar
navarrobr
Frequent Visitor
10 years ago
Solved

Calculate Overtime

I have the following table representing available and worked hours for each employee, in each day: Date Employee Hours Hour Type 01/05/2016  Marcelo 8 available 01/05/2016 Marcelo 9...
Need Help
  • v-qiuyu-msft's avatar
    v-qiuyu-msft
    10 years ago

    Hi navarrobr,

     

    In your scenario, you can create two new tables 'Woked' and 'Available', then build a relationship between those two tables. Please follow steps below:

     

    1. Create 'Available' table.

     

    Available = SUMMARIZE('Table','Table'[Date],"Available Hours",CALCULATE(SUM('Table'[Hours]),'Table'[Hour Type]="available"))

     

     

     

    2. Create 'Worked' table' and a calculated column 'OverTime'.

     

    Worked = SUMMARIZE('Table','Table'[Date],"Worked Hours",CALCULATE(SUM('Table'[Hours]),'Table'[Hour Type]="worked"))

     

    OverTime = IF('Worked'[Worked Hours]>RELATED('Available'[Available Hours]),[Worked Hours]-RELATED('Available'[Available Hours]),0)

     

     

    3. Build relationships.

     

     

     

    4. Create a table visual.

     

     

    If you have any question, please feel free to ask.

     

    Best Regards,
    Qiuyun Yu

Greg_Deckler's avatar
Greg_Deckler
Community Champion
10 years ago

I suspect that it is 0 in the total line because in the total line, your available hours exceed your worked hours. I'd have to test to be sure, but that is probably what is going on. You may have to ignore it and put a card visualization that just sums your Overtime.

navarrobr's avatar
navarrobr
Frequent Visitor
10 years ago

Yes, this is probably what is going on. But i really need to perform this calculation in the pivot table, my guess is that the formula in the measure "Overtime" is wrong.