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! 🙂

v-robertq-msft's avatar
v-robertq-msft
Community Support
5 years ago

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:

  1. Create two measures:
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]
  1. Set their measure format to “Percentage”, like this:

 

  1. Create a 100% stacked column chart and place the two measures, like this:

 

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.

littlemojopuppy's avatar
littlemojopuppy
Community Champion
5 years ago

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 😉