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

 

StudentIDSchool YearTermMarkD/F
12020S1A0
12020S1B0
12020S1A0
12020S1D1
12020S1F1
12020S1D1
22020S1A0
22020S1B0
22020S1D1
22020S1D1
22020S1F1
22020S1D1
32020S1A0
32020S1A0
32020S1A0
32020S1A0
32020S1A0
32020S1A0
42020S1C0
42020S1C0
42020S1C0
42020S1C0
42020S1D1
42020S1D1
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! 🙂

6 Replies

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

       

       

    • littlemojopuppy's avatar
      littlemojopuppy
      Community Champion
      5 years ago

      Anonymous I'm going to need you to convince my wife of that!  😉

      Glad I could help!

  • 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 😉