cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
afaherty
Helper III
Helper III

Calculate # and % students who NEVER passed (can take test multiple times)

Hello all,

I am sorry to be so needy around here lately!  I truly appreciate everyone's help so much!  I am now looking for a way to calculate the total # and % of distinct students who NEVER passed their English and Math tests, both of which they can take as many times as they want.  So the result should be:

 Total Never Passed% Never Passed
English630%
Math525%

 

Dummy data:

 

IDDate of TestTestScorePass Fail
12/14/2020English145Fail
13/9/2020English157Fail
18/3/2020English163Pass
27/22/2020English163Fail
32/6/2020English175Fail
33/6/2020English175Fail
46/27/2020English156Fail
44/5/2021English161Pass
53/16/2019English177Pass
66/5/2020English178Pass
76/11/2019English185Pass
89/30/2019English152Fail
87/12/2020English159Fail
811/8/2020English163Pass
911/12/2019English166Fail
101/8/2020English169Fail
102/8/2020English169Fail
112/13/2021English163Pass
1210/5/2019English157Fail
121/9/2020English167Pass
131/13/2020English169Pass
142/8/2020English163Fail
153/15/2019English181Pass
161/10/2020English150Fail
1612/30/2020English153Fail
178/8/2019English186Pass
188/5/2019English175Pass
192/4/2020English155Fail
193/14/2020English159Fail
196/7/2020English161Pass
209/30/2019English161Pass
12/14/2020Math145Fail
13/9/2020Math157Fail
18/3/2020Math163Pass
27/22/2020Math163Fail
28/22/2020Math163Fail
33/6/2020Math175Fail
46/27/2020Math156Fail
44/5/2021Math161Pass
53/16/2019Math177Pass
66/5/2020Math178Pass
76/11/2019Math185Pass
89/30/2019Math152Fail
87/12/2020Math159Fail
811/8/2020Math163Pass
911/12/2019Math166Fail
101/8/2020Math169Fail
112/13/2021Math163Pass
1210/5/2019Math157Fail
121/9/2020Math167Pass
131/13/2020Math169Pass
142/8/2020Math163Fail
143/8/2020Math163Fail
153/15/2019Math181Pass
161/10/2020Math175Pass
1612/30/2020Math161Pass
178/8/2019Math186Pass
188/5/2019Math175Pass
192/4/2020Math155Fail
193/14/2020Math159Fail
196/7/2020Math161Pass
209/30/2019Math161Pass
1 ACCEPTED SOLUTION
AlexisOlson
Super User
Super User

Take the total count of distinct IDs and subtract out the ones that have a "Pass".

 

Count Never Passed =
DISTINCTCOUNT ( Tests[ID] )
    - CALCULATE (
        DISTINCTCOUNT ( Tests[ID] ),
        FILTER ( Tests, Tests[Pass Fail] = "Pass" )
    )

% Never Passed = [Count Never Passed] / DISTINCTCOUNT ( Tests[ID] )

View solution in original post

2 REPLIES 2
AlexisOlson
Super User
Super User

Take the total count of distinct IDs and subtract out the ones that have a "Pass".

 

Count Never Passed =
DISTINCTCOUNT ( Tests[ID] )
    - CALCULATE (
        DISTINCTCOUNT ( Tests[ID] ),
        FILTER ( Tests, Tests[Pass Fail] = "Pass" )
    )

% Never Passed = [Count Never Passed] / DISTINCTCOUNT ( Tests[ID] )

Thank you!!

Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

May 23 2022 epsiode 5 without aka link.jpg

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

Power BI Dev Camp Session 22 with aka link 768x460.jpg

Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!