Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
krixtsup3r
Helper V
Helper V

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
v-juanli-msft
Community Support
Community Support

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
v-juanli-msft
Community Support
Community Support

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.

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.

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

 

Appreciate your help! 🙂 

rajulshah
Super User
Super User

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. 

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

@krixtsup3r,

 

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.