Forum Discussion

Figment98's avatar
Figment98
New Member
4 years ago

Calculation on Matrix Values (Matrix Value Shows Weekly Total Hours, but I need to show weekly OT)

Hi - I'm pretty new to Power BI, but I'm using it to bring together data from three sources:   Employee Data (including EE ID, FLSA status, EE status, etc.) Payroll Calendar Data (including weeks...
Need Help
DataInsights's avatar
DataInsights
Super User
4 years ago

Figment98,

 

Try this solution.

 

Data model:

 

 

Measures:

 

Total Hours = SUM ('Time'[hours_actual] )
Total OT = 
VAR vBaseTable =
    SUMMARIZE ( 'Time', Employee[Employee ID], 'Calendar'[Week End] )
VAR vCalcTable =
    ADDCOLUMNS (
        vBaseTable,
        "@OT",
            VAR vTotalHours = [Total Hours]
            RETURN
                IF ( vTotalHours > 40, vTotalHours - 40 )
    )
VAR vResult =
    SUMX ( vCalcTable, [@OT] )
RETURN
    vResult

 

In the matrix, use fields from the Calendar and Employee tables, and the measure [Total OT]: