DAX Measure for Calculating Amounts Across Hierarchy Levels

Question

I need to create a measure to calculate an employee's capped labour amount when he worked over 40 hours a week.

I have a measure named "Week Worked Hours," which provides the total worked hours for the week of an employee. And I reduce the worked amount of the employee of the date based on the 40 hours capped rate with the "Capped Worked Amount" measure. The capped amount is calculated correctly at the employee level but not when I roll up to department level and total.

Per the example below, the "Capped Worked Hour" measure on the far right is calculating correctly per employee. i.e. Bob has worked 66.66 hours in the week between 6th and 10th hence the worked amount has been reduced proportionally each day at the 40 hours/week capped. However, the total at the bottom of the "Capped Worked Hour" column is not summing up correctly.

Here is the DAX script for the measures:

Week Worked Hours = 
CALCULATE(
    // To calculate the week worked hours
    DIVIDE(SUM(LabourCost[DurationInSeconds]),3600,0),
    ALLEXCEPT('Calendar','Calendar'[First_Date_of_Week]),
    ALLEXCEPT(Employee,Employee[Employee_Name]),

    LabourCost[LineType] = "Actual",

    'Calendar'[First_Date_of_Week] = SELECTEDVALUE('Calendar'[First_Date_of_Week]
))

Capped Worked Amount = 
IF([Week Worked Hours] > 40,
    CALCULATE(DIVIDE([Worked Amount],[Week Worked Hours])*40),
    CALCULATE([Worked Amount])
)

I have also provided the sample data with the .pbix file: https://mega.nz/file/n4MkjJJZ#CcU8K6LFeQCZIu59QGqRgGMIvJDKsfYH7gOxnP_wSH4

---

Further to the answer below. I created this measure which tries to combine all the additional measures I created in the .pbix file into a single measure. And this does not return the same result:

Total Capped Wokrd Amount v2 = 
VAR vEmployeeRosterActualHourWeek = 
CALCULATE(
    // To calculate the week worked hours
    DIVIDE(SUM(LabourCost[DurationInSeconds]),3600,0),
    ALLEXCEPT('Calendar','Calendar'[First_Date_of_Week]),
    ALLEXCEPT(Employee,Employee[Employee_Name]),

    LabourCost[LineType] = "Actual",

    'Calendar'[First_Date_of_Week] = SELECTEDVALUE('Calendar'[First_Date_of_Week])
)

VAR vEmployeeCappedAmount = 
IF(vEmployeeRosterActualHourWeek > 40,
    CALCULATE(DIVIDE([Worked Amount],vEmployeeRosterActualHourWeek)*40),
    CALCULATE([Worked Amount])
)

RETURN 
SUMX(
    SUMMARIZECOLUMNS('Calendar'[First_Date_of_Week],Employee[Employee_Name]), 
    vEmployeeCappedAmount   
)

Answer 1

If I understood correctly, create a new measure with:

Total Capped Worked Amount =
  SUMX(
    SUMMARIZECOLUMNS('Calendar'[First_Date_of_Week], 'LabourCost'[Employee_ID]),
    [Capped Worked Amount]
  )

Update
If you must have it in one measure, then you need to move the VARs inside the SUMX so that they are calculated in context.

Total Capped Wokrd Amount v2 = 
  SUMX(
    SUMMARIZECOLUMNS('Calendar'[First_Date_of_Week],Employee[Employee_Name]), 

    VAR vEmployeeRosterActualHourWeek = 
      CALCULATE(
        // To calculate the week worked hours
        DIVIDE(SUM(LabourCost[DurationInSeconds]),3600,0),
        LabourCost[LineType] = "Actual"
      )
    VAR vEmployeeCappedAmount = 
      IF(vEmployeeRosterActualHourWeek > 40,
        CALCULATE(DIVIDE([Worked Amount],vEmployeeRosterActualHourWeek)*40),
        CALCULATE([Worked Amount])
      )
    RETURN vEmployeeCappedAmount
  )