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 and pay period parameters)
  • Time Data (individual time records from case management system)

 

I'm trying to build a Matrix Visualization that shows OT per week by employee, like this:

 

EE IDWk 1Wk 2Total
AB1230.020.051.25
BC23401.011.01
CD3453.081.959.83

 

I'm using a Matrix visualization because it looks to function much like a pivot table in Excel, and will allow me to do SUM all of the records for a single employee (ROW) for the week (COLUMN).  The source data doesn't include a total by week, so I need to use Power BI to automate that calculation (which could be anything from 5 unique entries to 50 unique time entries for a week).  

 

I've gotten to the point where I can get the total by week in the Matrix:

EE IDWk 1Wk 2Total
AB12340.0240.0580.07
BC23439.9541.0180.96
CD34543.0841.9585.03

 

But I'm unsure of how to take this result and get it down to the OT for the week.  I can't simply take Total - 80 (40 hours x 2 weeks) because OT is calculated on a weekly basis ONLY on hours over 40.  In the example of EE BC234, the total OT should actually be 1.01, not the 0.96 that would result from Total - 80.  

 

So I really need to see if EACH WEEK is over 40 before I can total the OT for the period.  The formula needs to operate like this: =IF(VALUE<40,0,(VALUE-40)).

 

Is there a way to do a calculation like that in a Matrix?  Is there a better way to sum all the time entry records ('Time'[hours_actual]) by employee ('Employee'[Employee ID]) for each week ('Payroll'[Week Start])?

 

 

 

Thanks for any assistance or links to resources that might help.  Appreciate it!  

Need Help

1 Reply

  • 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]: