dax calculation

[descalabro]

Hello,

I'm trying to put together a system report 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.

[v-sihou-msft · Accepted solution]

@descalabro

In your scenario, you want to sum values which is from "another calculated measure minus 8", you need to use the calculated table to build your calculated measure into a column so that it can be aggregated properly. Otherwise, it will always sum the total working hours and minus 8 as the result you get on Grand Total level.

I assume you have a table like below:

Then just create a calculated table with SUMMARIZE() function.

Table 2 = SUMMARIZE(
'Table','Table'[Date],
"Working Hours",[Total Working Hours],
"OT",IF([Total Working Hours]>8,[Total Working Hours]-8,BLANK())
)

Regards,

[descalabro]

@v-sihou-msft thank you!. Even tough I had that problem with the presentation of individual values, your solution worked like a charm:

New table code:

Extra = SUMMARIZE(
'Relatorios Equipa'; 'Relatorios Equipa'[Dia]; 'Relatorios Equipa'[Calendar Name];
"Horas Trabalhadas"; [Horas Trabalhadas];
"Horas Extra"; IF([Horas Trabalhadas]>8;[Horas Trabalhadas]-8;BLANK())
)

[Calendar Name] is so I can filter between different employees. Also, for those, like me, unexperienced with Simon's Solution, remember to connect the new table:

[descalabro]

Thank you all who have replied,

I was trying to apply @v-sihou-msft solution when I stumbled uppon a problem: the records that show up in my table visual don't correspond to the records in my base PBI table. See for example day 29-12-2016:

Duration is the original record in minutes for each task. For some reason Power BI is ignoring any repeated value for the same day. It doesn't affect the actual total of hours, but it's confusing me a lot. As you can see here (in hours):

All data is in the correct format (date, integer, decimal, decimal, text), so I have no idea why he's "grouping" equal duration values.

[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

[MattAllington]

The issue is they way filter context operates.  You need to do the calculation at the day level - this can be done with SUMX

Try this

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