cancel
Showing results for
Did you mean:
Highlighted
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:

 ID Employee Name Supervisor Units Time Unit Pass? 160 Aydin Samuels Fern Bowden 7258 59.3 1 138 Lesley Rigby Kyla Taylor 7149 50.8 1 132 Annette Hancock Carrie Austin 7042 57.8 1 196 Safah Medina Kyla Taylor 7031 79.5 1 158 Aqsa Yoder Carrie Austin 7030 49.3 1 140 Elen Piper Fern Bowden 7026 44.8 1 124 Inez Marks Kyla Taylor 6885 91.5 1 276 Neve Walters Fern Bowden 6744 62.9 0 221 Ayesha Kaur Kyla Taylor 6542 91.8 0 148 Nathanael Goulding Carrie Austin 6510 82.9 0 181 Tina Sherman Fern Bowden 6331 41.2 0 223 Charlize Mitchell Kyla Taylor 6290 46.2 0 255 Millie-Rose Rollins Carrie Austin 6070 37.8 0

My Expected output:

 AVERAGE (UNIT TARGET) 6762

 Supervisor Pass Count Total Employee Pass Rate Fern Bowden 2 4 50.0% Kyla Taylor 3 5 60.0% Carrie Austin 2 4 50.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

Re: Population Pass Rate

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

Re: Population Pass Rate

Hello @krixtsup3r,

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

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

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

New Contributor

Re: Population Pass Rate

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

Community Support

Re: Population Pass Rate

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.

Frequent Visitor

Re: Population Pass Rate

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

Frequent Visitor

Re: Population Pass Rate

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.

Announcements

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!

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?

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