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

[afaherty]

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
English	6	30%
Math	5	25%

Dummy data:

ID	Date of Test	Test	Score	Pass Fail
1	2/14/2020	English	145	Fail
1	3/9/2020	English	157	Fail
1	8/3/2020	English	163	Pass
2	7/22/2020	English	163	Fail
3	2/6/2020	English	175	Fail
3	3/6/2020	English	175	Fail
4	6/27/2020	English	156	Fail
4	4/5/2021	English	161	Pass
5	3/16/2019	English	177	Pass
6	6/5/2020	English	178	Pass
7	6/11/2019	English	185	Pass
8	9/30/2019	English	152	Fail
8	7/12/2020	English	159	Fail
8	11/8/2020	English	163	Pass
9	11/12/2019	English	166	Fail
10	1/8/2020	English	169	Fail
10	2/8/2020	English	169	Fail
11	2/13/2021	English	163	Pass
12	10/5/2019	English	157	Fail
12	1/9/2020	English	167	Pass
13	1/13/2020	English	169	Pass
14	2/8/2020	English	163	Fail
15	3/15/2019	English	181	Pass
16	1/10/2020	English	150	Fail
16	12/30/2020	English	153	Fail
17	8/8/2019	English	186	Pass
18	8/5/2019	English	175	Pass
19	2/4/2020	English	155	Fail
19	3/14/2020	English	159	Fail
19	6/7/2020	English	161	Pass
20	9/30/2019	English	161	Pass
1	2/14/2020	Math	145	Fail
1	3/9/2020	Math	157	Fail
1	8/3/2020	Math	163	Pass
2	7/22/2020	Math	163	Fail
2	8/22/2020	Math	163	Fail
3	3/6/2020	Math	175	Fail
4	6/27/2020	Math	156	Fail
4	4/5/2021	Math	161	Pass
5	3/16/2019	Math	177	Pass
6	6/5/2020	Math	178	Pass
7	6/11/2019	Math	185	Pass
8	9/30/2019	Math	152	Fail
8	7/12/2020	Math	159	Fail
8	11/8/2020	Math	163	Pass
9	11/12/2019	Math	166	Fail
10	1/8/2020	Math	169	Fail
11	2/13/2021	Math	163	Pass
12	10/5/2019	Math	157	Fail
12	1/9/2020	Math	167	Pass
13	1/13/2020	Math	169	Pass
14	2/8/2020	Math	163	Fail
14	3/8/2020	Math	163	Fail
15	3/15/2019	Math	181	Pass
16	1/10/2020	Math	175	Pass
16	12/30/2020	Math	161	Pass
17	8/8/2019	Math	186	Pass
18	8/5/2019	Math	175	Pass
19	2/4/2020	Math	155	Fail
19	3/14/2020	Math	159	Fail
19	6/7/2020	Math	161	Pass
20	9/30/2019	Math	161	Pass

[AlexisOlson · Accepted solution]

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

[afaherty]

Thank you!!