Total of several calculations by day.

[descalabro]

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:

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

Thank you.

[tringuyenminh92]

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)

[descalabro]

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.

[Baskar]

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 · Accepted solution]

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