Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 | worked |
02/05/2016 | Marcelo | 8 | available |
02/05/2016 | Marcelo | 5 | worked |
03/05/2016 | Marcelo | 8 | available |
03/05/2016 | Marcelo | 9 | 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:
Date | Employee | Available Hours | Worked Hours |
01/05/2016 | Marcelo | 8 | 9 |
02/05/2016 | Marcelo | 8 | 5 |
03/05/2016 | Marcelo | 8 | 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:
Date | Employee | Available Hours | Worked Hours | Overtime |
01/05/2016 | Marcelo | 8 | 9 | 1 |
02/05/2016 | Marcelo | 8 | 5 | 0 |
03/05/2016 | Marcelo | 8 | 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?
Solved! Go to Solution.
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
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
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".
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"
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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |