Forum Discussion
Lookup with Measure
Hi
I work in a school and am creating some headline measures for my post-16 cohort. All grades have point values attached to them, e.g; A* = 60; A = 50 etc. I have a calculated overall measure for average point score. I now need to show this as a grade based of DfE technical guidelines sample below:
| APS Band Min | APS Band Max | Grade |
| 58.34 | 60 | A* |
| 55 | 58.33 | A*- |
| 51.67 | 54.99 | A+ |
| 48.34 | 51.66 | A |
| 45 | 48.33 | A- |
| 41.67 | 44.99 | B+ |
| 38.34 | 41.66 | B |
| 35 | 38.33 | B- |
| 31.67 | 34.99 | C+ |
| 28.34 | 31.66 | C |
| 25 | 28.33 | C- |
| 21.67 | 24.99 | D+ |
| 18.34 | 21.66 | D |
| 15 | 18.33 | D- |
| 11.67 | 14.99 | E+ |
| 8.34 | 11.66 | E |
| 5 | 8.33 | E- |
| 0 | 4.99 | U |
For example I have an A level APS as a measure of 30.33 - I need to create a measure to show the equivalent grade. How can I do this?
Hi gill_thornton,
I have made a simple model with only the grades table and a metric with a value input, no other tables or relationships.
How are you calculating the metric? is there any relationship between the grades and the table from where you are calculating the metric?
5 Replies
gill_thornton Not sure if I understand completely but maybe something like a SWITCH(TRUE(), ...) statement like:
Measure = SWITCH(TRUE(), [Current Measure] > 45, "A", [Current Measure] > 35, "B", [Current Measure] > 25, "C", [Current Measure] > 15, "D", "F" )Hi gill_thornton,
Taking into account that you have the table above for your grade you need to do something similar to this:
Grade Value = MINX ( FILTER ( 'Grade Table', 'Grade Table'[APS Band Min] <= [A level APS] && 'Grade Table'[APS Band Max] >= [A level APS] ), 'Grade Table'[Grade] )Thank you for this solution. It looks as though it should work perfectly, but when I apply, it is returning a grade A, which is incorrect. I'm not quite sure where I'm going wrong.
Hi gill_thornton,
I have made a simple model with only the grades table and a metric with a value input, no other tables or relationships.
How are you calculating the metric? is there any relationship between the grades and the table from where you are calculating the metric?
