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.
I hope I'm wording this correctly. I have a data request to show the percentage of students that earned a D or F on their latest report card.
I want the end user to be able to use a parameter or something like that so they can select a minimum threshold that calculates the percentage of students that meet or exceed that threshold. Using the sample data below, if the end user selected 2, then the 100% stacked column chart would show 75% of the students have 2 or more Ds or Fs and 25% do not. If 3 were selected, then it would recalculate to show 50% have 3 or more Ds/Fs and 50% do not.
Below is a copy of the sample data and here a link to the sample pbix file:
https://drive.google.com/file/d/1fPdCS7gRuaHimtxKiP7K9PktJmpKUVsT/view?usp=sharing
StudentID | School Year | Term | Mark | D/F |
1 | 2020 | S1 | A | 0 |
1 | 2020 | S1 | B | 0 |
1 | 2020 | S1 | A | 0 |
1 | 2020 | S1 | D | 1 |
1 | 2020 | S1 | F | 1 |
1 | 2020 | S1 | D | 1 |
2 | 2020 | S1 | A | 0 |
2 | 2020 | S1 | B | 0 |
2 | 2020 | S1 | D | 1 |
2 | 2020 | S1 | D | 1 |
2 | 2020 | S1 | F | 1 |
2 | 2020 | S1 | D | 1 |
3 | 2020 | S1 | A | 0 |
3 | 2020 | S1 | A | 0 |
3 | 2020 | S1 | A | 0 |
3 | 2020 | S1 | A | 0 |
3 | 2020 | S1 | A | 0 |
3 | 2020 | S1 | A | 0 |
4 | 2020 | S1 | C | 0 |
4 | 2020 | S1 | C | 0 |
4 | 2020 | S1 | C | 0 |
4 | 2020 | S1 | C | 0 |
4 | 2020 | S1 | D | 1 |
4 | 2020 | S1 | D | 1 |
Solved! Go to Solution.
Hello @adamwakatsuki. Try this...
Percent Students With Failing Grades =
VAR TotalStudents =
CALCULATE(
DISTINCTCOUNT(Marks[StudentID]),
FILTER(
ALLSELECTED(Marks),
Marks[School Year] = SELECTEDVALUE(Marks[School Year]) &&
Marks[Term] = SELECTEDVALUE(Marks[Term])
)
)
VAR StudentTermSummary =
SUMMARIZE(
FILTER(
ALLSELECTED(Marks),
Marks[School Year] = SELECTEDVALUE(Marks[School Year]) &&
Marks[Term] = SELECTEDVALUE(Marks[Term])
),
Marks[StudentID],
Marks[School Year],
Marks[Term],
"FailingGrades",
CALCULATE(
COUNTROWS(Marks),
Marks[Mark] IN {"D", "F"}
) + 0
)
VAR StudentsWithFailingGrades =
CALCULATE(
DISTINCTCOUNT(Marks[StudentID]),
FILTER(
StudentTermSummary,
[FailingGrades] >= SELECTEDVALUE('Number of Failing Grades'[Number of Failing Grades])
)
)
RETURN
DIVIDE(
StudentsWithFailingGrades,
TotalStudents,
BLANK()
)
I hope this helps! 🙂
Hi, @Anonymous
According to your description, I can understand what you want to get. You want to get a dynamic 100% stacked column chart based on the selected value of the Slicer. You can try my steps:
Students earned more than selected number =
var _summarize=
SUMMARIZE('Marks',[StudentID],"D/F",SUM(Marks[D/F]))
var _selectedvalue=SELECTEDVALUE('Number of Failing Grades'[Number of Failing Grades])
var _total=DISTINCTCOUNT('Marks'[StudentID])
var _count=
COUNTX(FILTER(_summarize,[D/F]>=_selectedvalue),[StudentID])
return
DIVIDE(_count,_total)
Students earned less than selected number =
1-[Students earned more than selected number]
And you can get what you want.
You can download my test pbix file here
Best Regards,
Community Support Team _Robert Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@v-robertq-msft @ I already delivered a solution on this. @Anonymous @Said I was awesome and I asked him to convince my wife of that! 🙂. He just needs to mark my suggestion as the solution 😉
@Anonymous I'm going to need you to convince my wife of that! 😉
Glad I could help!
Hello @adamwakatsuki. Try this...
Percent Students With Failing Grades =
VAR TotalStudents =
CALCULATE(
DISTINCTCOUNT(Marks[StudentID]),
FILTER(
ALLSELECTED(Marks),
Marks[School Year] = SELECTEDVALUE(Marks[School Year]) &&
Marks[Term] = SELECTEDVALUE(Marks[Term])
)
)
VAR StudentTermSummary =
SUMMARIZE(
FILTER(
ALLSELECTED(Marks),
Marks[School Year] = SELECTEDVALUE(Marks[School Year]) &&
Marks[Term] = SELECTEDVALUE(Marks[Term])
),
Marks[StudentID],
Marks[School Year],
Marks[Term],
"FailingGrades",
CALCULATE(
COUNTROWS(Marks),
Marks[Mark] IN {"D", "F"}
) + 0
)
VAR StudentsWithFailingGrades =
CALCULATE(
DISTINCTCOUNT(Marks[StudentID]),
FILTER(
StudentTermSummary,
[FailingGrades] >= SELECTEDVALUE('Number of Failing Grades'[Number of Failing Grades])
)
)
RETURN
DIVIDE(
StudentsWithFailingGrades,
TotalStudents,
BLANK()
)
I hope this helps! 🙂
I left out one thing...for your stacked column chart, you're going to need a second measure.
Other Measure = 1 - [Percent Students With Failing Grades]
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 |
---|---|
110 | |
94 | |
82 | |
66 | |
58 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |