Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to 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] )
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!!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
24 | |
20 | |
15 | |
13 |
User | Count |
---|---|
51 | |
46 | |
39 | |
19 | |
19 |