Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Dynamic 100% stacked column chart

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 ...
Need Help
  • littlemojopuppy's avatar
    littlemojopuppy
    5 years ago

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

    littlemojopuppy_0-1609960512854.png

    littlemojopuppy_1-1609960530877.png

    I hope this helps! 🙂

littlemojopuppy's avatar
littlemojopuppy
Community Champion
5 years ago

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

littlemojopuppy_0-1609960512854.png

littlemojopuppy_1-1609960530877.png

I hope this helps! 🙂

littlemojopuppy's avatar
littlemojopuppy
Community Champion
5 years ago

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]