cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
navarrobr
Frequent Visitor

Calculate Overtime

I have the following table representing available and worked hours for each employee, in each day:

DateEmployeeHoursHour Type
01/05/2016 Marcelo8available
01/05/2016Marcelo9worked
02/05/2016Marcelo8

available

02/05/2016Marcelo5

worked

03/05/2016Marcelo8

available

03/05/2016Marcelo9

worked

 

I created two measuares to represent the two different hour types "side by side":

 

Available Hours = calculate(sum(Table[Hours]);Table[Hour Type]="available")

Worked Hours = calculate(sum(Table[Hours]);Table[Hour Type]="worked")

 

Then I could create a pivot table like this:

 

DateEmployeeAvailable HoursWorked Hours
01/05/2016Marcelo89
02/05/2016Marcelo8

5

03/05/2016Marcelo8

9

TOTAL 24

23

 

Then i created a different measure, to calculate the overtime:

 

Overtime = if([Worked Hours]>[Available Hours];[Worked Hours]-[Available Hours];0)

 

The result was:

 

DateEmployeeAvailable HoursWorked HoursOvertime
01/05/2016Marcelo891
02/05/2016Marcelo8

5

0
03/05/2016Marcelo8

9

1
TOTAL 24

23

0

 

The problem is: the overtime is correct on the date level, but the result in the total level should be 2. What im I doing wrong?

 
2 ACCEPTED SOLUTIONS

Accepted Solutions
Moderator v-qiuyu-msft
Moderator

Re: Calculate Overtime

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

 

 

z1.PNG

 

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)

z2.PNG

 

 

3. Build relationships.

 

z5.PNG

 

 

4. Create a table visual.

 

z4.PNGz3.PNG

 

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

 

Best Regards,
Qiuyun Yu

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

navarrobr
Frequent Visitor

Re: Calculate Overtime

6 REPLIES 6
Super User IV
Super User IV

Re: Calculate Overtime

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.


---------------------------------------

Not the Power BI thought police...

I have NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

navarrobr
Frequent Visitor

Re: Calculate Overtime

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.

Super User IV
Super User IV

Re: Calculate Overtime

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


---------------------------------------

Not the Power BI thought police...

I have NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Super User IV
Super User IV

Re: Calculate Overtime

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"

---------------------------------------

Not the Power BI thought police...

I have NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Moderator v-qiuyu-msft
Moderator

Re: Calculate Overtime

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

 

 

z1.PNG

 

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)

z2.PNG

 

 

3. Build relationships.

 

z5.PNG

 

 

4. Create a table visual.

 

z4.PNGz3.PNG

 

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

 

Best Regards,
Qiuyun Yu

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

navarrobr
Frequent Visitor

Re: Calculate Overtime

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors