cancel
Showing results for
Did you mean: 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: Is there a way around this so I can get the total of extra work hours?

Thank you.

1 ACCEPTED SOLUTION  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
4 REPLIES 4  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)`  Frequent Visitor

Hello,

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.   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  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 Announcements #### Welcome to the User Group Public Preview #### 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. #### 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
Users online (2,879)