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

Total of several calculations by day.

Hello,

 

I'm trying to put together a system to report the time spent in Outlook tasks, specifically the total of work hours by day (the sum of the time spent with each task in a day), and the sum of work hours that surpass 8 work hours by day. I was able to create the following DAX measure:

 

 

HE = IF(CALCULATE( SUM('Relatorios Equipa'[Horas Trabalhadas]); ALLEXCEPT('Tempo'; Tempo[Data])) > 8; CALCULATE( SUM('Relatorios Equipa'[Horas Trabalhadas]); ALLEXCEPT('Tempo'; Tempo[Data])) - 😎

The same DAX in English:

 

HE = IF(CALCULATE( SUM('Team Report'[Work Hours]); ALLEXCEPT('Time'; Time[Date])) > 8; CALCULATE( SUM('Team Report'[Work Hours]); ALLEXCEPT('Time'; Time[Date])) - 😎

 

 

This DAX is meant to check for the total work hours (HT) within each day and, if greater than 8h, it will subtract 8h so the result will be the number of extra hours worked in a day (HE). It works, except that it aplies the same calculation rule to the total in the table, which means that it sums all the work hours and subtracts 8h, instead of doing the sum of the resulting extra work hours only. Here is an image:

work hours.png

 

Is there a way around this so I can get the total of extra work hours?

 

Thank you.

 

 

 

1 ACCEPTED SOLUTION
Vvelarde
Community Champion
Community Champion

@descalabro

 

hi, use this dax and let me know if helps

 

he =
VAR HET =
    CALCULATE ( SUM ( Table1[ht] ) - 8 )
RETURN
    IF (
        HASONEVALUE ( Table1[ht] ),
        IF ( HET > 0, HET, BLANK () ),
        SUMX (
            SUMMARIZE (
                Table1,
                Table1[calendar name],
                Table1[month],
                Table1[day],
                "hettotal", IF (
                    CALCULATE ( SUM ( Table1[ht] ) - 8 )
                        > 0,
                    CALCULATE ( SUM ( Table1[ht] ) - 8 ),
                    BLANK ()
                )
            ),
            [hettotal]
        )
    )



Lima - Peru

View solution in original post

4 REPLIES 4
tringuyenminh92
Memorable Member
Memorable Member

Hi @descalabro,

 

You could create calculated column to minus 8 hours row by row for extra hours, after that you could sum that calculated column by Card control.

 

HE = if(Effort[HT (Work Hours)]>8, Effort[HT (Work Hours)]-8,0)

Screenshot 2016-12-26 21.16.47.png

 

Hello,

 

Thank you for your reply.

 

The complexity here is that I'm summing several hours from different Outlook tasks for each day, that's why I'm using CALCULATE to filter my sum by date, so I can get the total hours for each day and subtract by 8 if the day's total surpasses 8. This is why I need to use SUM before checking if the result is greater than 8. I've tried to set to 0 all the days under or equal to 8h, but the total is not summing each day, it is using the DAX to sum all work hours from all work days and then subtracting 8h; because it is a total, it doesn't have a date to filter by.

 

work hours2.png

 

Baskar
Resident Rockstar
Resident Rockstar

Try this one dude.

 

Final_Result = CALCULATE( [HE], 

                                            FILTER( SUMMARIZE( 'Date', 'Date'[Calendar Name], 'Date'[Month],  [Day] ,

                                             "Counting",[HE]),
                                              SUM([Counting]) > 0)

                                           

 

Note :

          Replace the Bold Characters with your related column in that table.

 

 

If it will not help to solve your prob let me know , i will help u 

Vvelarde
Community Champion
Community Champion

@descalabro

 

hi, use this dax and let me know if helps

 

he =
VAR HET =
    CALCULATE ( SUM ( Table1[ht] ) - 8 )
RETURN
    IF (
        HASONEVALUE ( Table1[ht] ),
        IF ( HET > 0, HET, BLANK () ),
        SUMX (
            SUMMARIZE (
                Table1,
                Table1[calendar name],
                Table1[month],
                Table1[day],
                "hettotal", IF (
                    CALCULATE ( SUM ( Table1[ht] ) - 8 )
                        > 0,
                    CALCULATE ( SUM ( Table1[ht] ) - 8 ),
                    BLANK ()
                )
            ),
            [hettotal]
        )
    )



Lima - Peru

View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Top Solution Authors
Top Kudoed Authors