Hello,
I have the following dataset, called "Half-Band Base":
| school year | school | subject | student_id | time 2 term | time 2 performance level | time 1 performance level | Half-Band Growth |
| 2018 | ABC | Math | 1 | Spring | 3 | 2 | Growing |
| 2018 | ABC | Math | 2 | Spring | 4 | 4 | Holding Steady |
| 2018 | ABC | Math | 3 | Spring | 4 | 5 | Declining |
This dataset is compares a student's score in time 2 to their score in time 1 and identifies whether or not the student is growing.
For each school year, term, school building, and subject, I want to determine the percentage of students who are growing, holding steady, or declining. I create the following measure:
Percent of Students = DISTINCTCOUNT('Half Band Base'[student_id])/CALCULATE(DISTINCTCOUNT('Half Band Base'[student_id]), ALL('Half Band Base'[Half-Band Growth]))
This works when I create a table that contains school, subject, Half-Band Growth, and have a slicer for school year and term. Great.
Now I want to sort each building into a performance level based on the percentage of students in a building who are growing. The cut points are pre-established.
Absolute Performance Level = CALCULATE(IF('Half Band Base'[Percent of Students]>=.3915, "Exceeds Expectations", IF('Half Band Base'[Percent of Students]>=.33 && 'Half Band Base'[Percent of Students] <.3915, "Meets Expectations", IF('Half Band Base'[Percent of Students]>=.2685 && 'Half Band Base'[Percent of Students] <.33, "Approaching Expectations", IF('Half Band Base'[Percent of Students]<.2685, "Not Meeting Expectations")))),'Half Band Base'[Half-Band Growth]="Growing")
This works and correctly assigns a performance level to a school building. I can add this categorization to my table with school, subject, Half-Band Growth, and Percent of Students. Great.
Now I want to visualize the number of schools that fall into each category (Exceeds Expectations, Meets Expactations, Approaching Expectations, Not Meeting Expectations) in a bar chart, with the count of schools on the y axis and the Absolute Performance Level category on the X. I am stuck and cannot figure out how to do this. I tried creating a calculated table as follows, but I am getting an error. I have never made a calculated table before and I don't really understand what I am doing, tbh. Any recommendations? I am also open to doing things in a completely different way......
Absolute Performance Level Summary = ADDCOLUMNS(SUMMARIZE('Half Band Base', 'Half Band Base'[school_name], "Percent Half Band Group", [Percent of Students]),
"Absolute Performance Level", CALCULATE(IF('Half Band Base'[Percent of Students]>=.3915, "Exceeds Expectations", IF('Half Band Base'[Percent of Students]>=.33 && 'Half Band Base'[Percent of Students] <.3915, "Meets Expectations", IF('Half Band Base'[Percent of Students]>=.2685 && 'Half Band Base'[Percent of Students] <.33, "Approaching Expectations", IF('Half Band Base'[Percent of Students]<.2685, "Not Meeting Expectations")))),'Half Band Base'[Half-Band Growth]="Growing"))
