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

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] )

View solution in original post

Thank you!!

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!