Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
hi everyone,
i have the next issue:
on each workday people have to work 8 hours. Now i want to know how many hours overtime there are in a week.
In a VAR I calculate how many workdays there are in a period. This looks good, and also it used a calendar for national hollidays.
But.... It has to calculate the overtime for each week. Also when i selected a month i need to recognize for each seperate week how many overtime there is and give a total.
something like this
overtime_hours =
VAR Norm = CALCULATE(COUNTROWS(FILTER(Calendar,Calendar[Workday]="Yes"))*8)
RETRUN
CALCULATE(
SUM(Hours)-[Norm],
FILTER(Calendar,Calendar[Week])
)
Week | Norm | Hours | Overtime |
1 | 40 | 42 | 2 |
2 | 32 | 30 | 0 |
3 | 40 | 38 | 0 |
4 | 40 | 40 | 0 |
5 | 32 | 36 | 4 |
Total | 184 | 186 | 6 |
Can somebody help me?
With kind regards
Hi @Anonymous ,
We can use the following measure to meet your requirement.
Measure =
SUMX(GROUPBY('Table','Table'[name 1],'Table'[week]),[Overtime])
[Overtime] is your incorrect total measure. And the result like this,
If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?
BTW, pbix as attached.
Best regards,
Community Support Team _ zhenbw
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Greg_Deckler @v-zhenbw-msft
thanks for your ideas. Great.
I made a combination of it and gives the correct result
Overtime =
VAR Norm = CALCULATE(COUNTROWS(FILTER(kalender,kalender[Werkdag]="Ja"))*8)
Return
CALCULATE(
SUMX (
SUMMARIZE (
tvHosActivities,
tvHosActivities[Name],
kalender[Week],
"TotalHours", [100% uren]
),
IF ( [TotalHours] > [Norm], [TotalHours] - [Norm], 0 )
)
)
@Anonymous - You seem pretty close with what you have, the exact implementation will depend on how your source data is laid out. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.
But, put week number in a visual along with this maybe:
overtime_hours =
VAR Norm = COUNTROWS(FILTER(Calendar,Calendar[Workday]="Yes"))*8
RETURN
SUM([Hours]) - [Norm]
Have you tried doing this as 3 separate measures as in your desired output table? Variables are calculated when they are created, not when they are used, so you will sometimes get a different result when using a variable than you get by using a measure within a measure.
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
Hi,
seperate measures are always a good idea.
SUMX (
VALUES (kalender[Week] ),
IF ( [100% uren] > [Norm], [100% uren] - [Norm], 0 )
)
Result for each week is correct,
But now i want on higher level (several weeks / month / year) shows the total thats is calculated above the norm for each week. In this example 3,78 + 6,06
@Anonymous - Now it looks like you have a measures total problem perhaps. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376
Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907
User | Count |
---|---|
102 | |
91 | |
87 | |
79 | |
71 |
User | Count |
---|---|
113 | |
105 | |
101 | |
75 | |
64 |