Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Population Pass Rate

Hello Everyone,

 

Requesting for your help in creating a Population Pass Rate computation wherein the target is dynamic. Please see sample data below:

 

IDEmployee NameSupervisorUnitsTimeUnit Pass?
160Aydin SamuelsFern Bowden725859.31
138Lesley RigbyKyla Taylor714950.81
132Annette HancockCarrie Austin704257.81
196Safah MedinaKyla Taylor703179.51
158Aqsa YoderCarrie Austin703049.31
140Elen PiperFern Bowden702644.81
124Inez MarksKyla Taylor688591.51
276Neve WaltersFern Bowden674462.90
221Ayesha KaurKyla Taylor654291.80
148Nathanael GouldingCarrie Austin651082.90
181Tina ShermanFern Bowden633141.20
223Charlize MitchellKyla Taylor629046.20
255Millie-Rose RollinsCarrie Austin607037.80

 

My Expected output:

AVERAGE (UNIT TARGET)6762

 

SupervisorPass CountTotal EmployeePass Rate
Fern Bowden2450.0%
Kyla Taylor3560.0%
Carrie Austin2450.0%

 

In my Report, there is a date slicer wherein the total units of each employee changes. The target that I have for the units is based on the average of the population, so the target might always change

 

I have sucessfully created a measure to get the average of entire population & identify if the employee passed it or not but the problem comes in when I want to create a summary per supervisor to know:

  • How many passed on his/her team
  • How many is the employee under him/her
  • His total pass rate wherein the computation is Pass Count over Total Employee Count

The computation that I created to identify the employee pass it or not always shows 1 at the total. What I want to happen is to show the total # of people that pass it. 

 

Below are the Measures that I created:

Target-Volume = CALCULATE(AVERAGEX(ALLSELECTED(EmployeeName), CALCULATE(SUM(Units))), ALLSELECTED(MyTable))

PassFail = IF(SUM(Units) > [Target-Volume], 1, 0)

 

I hope I am in the right page, if not please let me know so that I can transfer it to the correct section.

 

Thank you so much and I appreciate the help or inputs that you might give.

Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    6 years ago

    Hi Anonymous 

    Create measures

     

    pass count =
CALCULATE (
    DISTINCTCOUNT ( 'Table'[Employee Name] ),
    FILTER (
        ALLSELECTED ( 'Table' ),
        'Table'[Supervisor]
            = MAX ( 'Table'[Supervisor] )
            && 'Table'[Unit Pass?] = 1
    )
)

total emp =
CALCULATE (
    DISTINCTCOUNT ( 'Table'[Employee Name] ),
    FILTER (
        ALLSELECTED ( 'Table' ),
        'Table'[Supervisor]
            = MAX ( 'Table'[Supervisor] )
    )
)


pass rate = [pass count]/[total emp]

     

    Best Regards
    Maggie
    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

6 Replies

  • rajulshah's avatar
    rajulshah
    Resident Rockstar
    6 years ago

    Hello Anonymous,

     

    I did not fully understand your issue, but are you facing issue calculating Pass rate?

    If yes, please use:

    Pass Rate = 
VAR PassCount =  CALCULATE(DISTINCTCOUNT(Table[ID]),Table[Unit Pass?]=1)
VAR TotalEmployees = DISTINCTCOUNT(Table[ID])
RETURN DIVIDE(PassCount,TotalEmployees)

     

    If not, please guide ahead. 

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi,

       

      Thank you for your prompt response and apologies if I wasn't able to explain it better.

       

      What I really need is:

      - A dynamic target that changes based on the selected date coverage. The target is the average of all the data per employee.

      - Pass Count measure if the employee passes (value is 1 or 0), the total should also show how many passed

       

      Lastly, a table that will summarize this by supervisor shown in the table above

      • rajulshah's avatar
        rajulshah
        Resident Rockstar
        6 years ago

        Anonymous,

         

        I cannot understand. Can you show your current data vs expected data?

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    6 years ago

    Hi Anonymous 

    Create measures

     

    pass count =
CALCULATE (
    DISTINCTCOUNT ( 'Table'[Employee Name] ),
    FILTER (
        ALLSELECTED ( 'Table' ),
        'Table'[Supervisor]
            = MAX ( 'Table'[Supervisor] )
            && 'Table'[Unit Pass?] = 1
    )
)

total emp =
CALCULATE (
    DISTINCTCOUNT ( 'Table'[Employee Name] ),
    FILTER (
        ALLSELECTED ( 'Table' ),
        'Table'[Supervisor]
            = MAX ( 'Table'[Supervisor] )
    )
)


pass rate = [pass count]/[total emp]

     

    Best Regards
    Maggie
    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Thank you! I will try this one later and will let you know if it works.

       

      Appreciate your help! 🙂 

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi v-juanli-msft,

       

      Thank you. I think this is the right route but I have one more problem this:

      'Table'[Unit Pass?]

       is actually nonexistent to my table, I also need a formula that does this.