cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
krixtsup3r Frequent Visitor
Frequent Visitor

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support
Community Support

Re: Population Pass Rate

Hi @krixtsup3r 

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]

 

Capture1.JPG

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.

View solution in original post

6 REPLIES 6
rajulshah New Contributor
New Contributor

Re: Population Pass Rate

Hello @krixtsup3r,

 

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. 

krixtsup3r Frequent Visitor
Frequent Visitor

Re: Population Pass Rate

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 New Contributor
New Contributor

Re: Population Pass Rate

@krixtsup3r,

 

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

Community Support
Community Support

Re: Population Pass Rate

Hi @krixtsup3r 

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]

 

Capture1.JPG

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.

View solution in original post

krixtsup3r Frequent Visitor
Frequent Visitor

Re: Population Pass Rate

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

 

Appreciate your help! 🙂 

krixtsup3r Frequent Visitor
Frequent Visitor

Re: Population Pass Rate

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.

Helpful resources

Announcements
Coming Soon: T-Shirt Design Contest

Coming Soon: T-Shirt Design Contest

Keep your eyes open for our upcoming T-shirt design contest!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors
Top Kudoed Authors