cancel
Showing results for
Did you mean: Frequent Visitor

## Using a Measure in a Visualization

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"))``````

2 REPLIES 2  Super User

Where is the percent field coming from? (It's not part of the sample data you posted)

In doing so, you are also helping me. Thank you!

Proud to be a Super User! Frequent Visitor

Hi Paul, thanks for your question! Percent of Students is a measure. The DAX for the measure is in the first snippet of code. That is what I am referencing in the subsequent code entries.  