Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
rachaellearns
Frequent Visitor

Using a Measure in a Visualization

Hello, 

 

I have the following dataset, called "Half-Band Base":

school yearschoolsubjectstudent_id time 2 termtime 2 performance leveltime 1 performance levelHalf-Band Growth
2018ABCMath1Spring32Growing
2018ABCMath2Spring44Holding Steady
2018ABCMath3Spring45Declining

 

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
PaulDBrown
Community Champion
Community Champion

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





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






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. 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

Top Solution Authors