Calculated Column to Filter the sum total of Regular Hours over 40

[Anonymous]

Hi,

I’m trying to create a column to error-check regular timesheet hours that are over 40 for a week.

Columns in the table (TESS):

Employee ID [EMPL_ID]

End of Week TESS[End of Week] 

Type of Hours TESS[TYPE] 

Entered Hours TESS[ENTERED_HRS] 

I’ve tried

R Hours Over 40 =

CALCULATE (

    SUM ( TESS[ENTERED_HRS] ),

    TESS[TYPE] = "R",

    ALLEXCEPT ( TESS, TESS[End of Week] )

)

This is not giving me the results I need. 

I can create a new table and get the desired results:

TESS_R_BY_EOW =

VAR baseTable =

    CALCULATETABLE (

        SUMMARIZECOLUMNS (

            TESS[EMPL_ID],

            TESS[End of Week]         

        ),

        TESS[TYPE] = "R"

    )

RETURN

    GENERATE (

        baseTable,

        VAR R_hours =

            CALCULATE ( SUM ( TESS[ENTERED_HRS] ), TESS[TYPE] = "R" )

        RETURN

            ROW ( "R Hours", R_hours )

    )

Any help will be greatly appreciated.

VR

Brad

[V-lianl-msft · Accepted solution]

Please try:

R Hours Over 40 = 
IF(TESS[TYPE] = "R",
    CALCULATE(SUM(TESS[ENTERED_HRS]),
        FILTER(
            ALLEXCEPT(TESS,TESS[EMPL_ID],TESS[End of Week]),
            TESS[TYPE] = "R")),
    BLANK())