Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Norbertus
Helper V
Helper V

total only with filtered data on week level - overtime

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

 

 

WeekNormHoursOvertime
140422
232300
340380
440400
532364
Total1841866

 

Can somebody help me?

 

With kind regards

 

 

 

6 REPLIES 6
v-zhenbw-msft
Community Support
Community Support

Hi @Norbertus ,

 

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,

 

Total 1.jpg

 

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

 

Greg_Deckler
Super User
Super User

@Norbertus - 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]

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
AllisonKennedy
Super User
Super User

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. 

 


Please @mention me in your reply if you want a response.

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

 

 

example.png
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

 

@Norbertus - 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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.