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 confirmed that the last column does not seem to be totaling for the column but rather doing the calculation for that Measure using the values in the Total row. I tested this by changing one of your 9's to a 19. My Overtime total (last row) ended up being 9, which was the difference between the total available and worked hours.

 

Should it work like this? Probably not I would suspect. You could file it under "Issues".

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    10 years ago

    Here is what I would do. Delete all of your measures. Edit your query and pivot on your "Hour Type" column with "Hours" as your values column. Then, you just create a custom column for Overtime and put everything in a Table. No need for pivot tables or matrices and all the numbers work.

     

    Overtime Column:

    OvertimeHours = if([worked]>[available],[worked]-[available],0)

     

     

    Here is the test query I used:

     

    let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUNzDVNzIwNFPSUfJNLEpOzckHsiyAOLEsMTMnMSknVSlWB6dCSyAuzy/KTk2BqDIi1jjsCk3RjDMm1jjsCpFdFwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t, Employee = _t, Hours = _t, #"Hour Type" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Employee", type text}, {"Hours", Int64.Type}, {"Hour Type", type text}}),
#"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[#"Hour Type"]), "Hour Type", "Hours", List.Sum)
in
#"Pivoted Column"