I need you assistance on this measure that I am working.
I want to make a dynamic Supervisor Score card. Where on PowerBI Visual I have a month Slicer, to show the score of each team leader, the score is being calculated by determining if employee has a score of 4 or 5, it means they passed. if 3 or below, the employee failed. it the supervisor has 10 employees and 6 of them got a 4 above score, the supervisor’s score should be 60%. the reason why it should be dynamic is because, if the employee is terminated on march 15, 2024, when you select January or February on the month slicer, the employees should still be counted on the supervisor’s score, but, if march is selected, this employee should be remove from the supervisor’s score

I have the following table.

Name Mapping = it has the name of the employee, Their LOB, start date, regularization date, termination date (blank if not terminated)

Attendance = this is where the attendance of each employee is being recorded. since attendance table has a daily record of each employee, this what I use to capture the employee stats, this is also where, you will determine is the employee is terminated and when is the employee was regularized, I already created a column measure to determine if the employee is eligible to counted on Supervisor’s scorecard.

`Eligibility Status = 

 IF(RELATED('Terminated Employees'[Termination Date]) = BLANK()
 &&
 RELATED('Name Mapping'[Incentive Eligibility date]) < Attendance[Date]
 ,"Active",
 SWITCH(TRUE(),
 RELATED('Terminated Employees'[Termination Date]) > Attendance[Date]

 ,

  "Active","Inactive")
 )`

calendar table = just a basic calendar measure

this is my measure to get the supervisor score:

`TL AODM Score non Incentive = 
VAR AODMTeam = {"Add-Ins", "Team Management"}
VAR FilteredTable = 
    FILTER('Name Mapping', 'Name Mapping'[LOB] IN AODMTeam)
VAR Scores = 
    CALCULATE(
        SUMX(
            FilteredTable,
            SWITCH(
                TRUE,
                'Name Mapping'[LOB] = "Add-Ins",
                    IF([Add-Ins Overall Score] >= 4, 1, 0),
                'Name Mapping'[LOB] = "Team Management",
                    IF([Team Management Score Card] >= 4, 1, 0),
                TRUE,  
                    0)))
VAR TotalCount = 
    CALCULATE(
        COUNTROWS(FilteredTable))
RETURN
    DIVIDE(
        Scores,
        TotalCount)`

currently it works on but it also add the score on the terminated employee on the supervisor’s score.
I understand that selectedvalue wont work on custom column. So I was thinking to make this work, I may need to make adjustment on the measure.

I was also thinking of using filter, on the page, but it does not work.

so any assistance will be greatly appreciated.