Create a calculated column based on mutiple fields, then a measure to determine %

[TravellingMagpi]

Hi, I'm looking to create a calculated column to track student reading progress by year, month, grade in both English and Arabic subjects - a simple yes / no will suffice.

Monthly we have a reading extract provided with the relevant fields appended to the previous months data which includes the current reading level of each student in each subject.  All of these are text fields.

@Reading Summary 

We have a seperate table which includes the Target Reading levels by year, month, grade, subject and scale.  As the targets and reading levels are text values, we included the field Scale to provide a numeric value.

@Reading Target 

So i need to add the calculated column to the monthly extract which checks the current English Reading Level against the target to determin yes / no, also checks the current Arabic Reading Level against target to determine yes / no.

Finally a meausre to calculate a what % of students are at the expected reading level.

Any assistance appreciated.  

[HotChilli · Accepted solution]

If it's a requirement to compare the reading level with a !=  (as opposed to scoring), the easiest way to return the Target from the Target table to the Summary table will be to use a LOOKUPVALUE.  I've had a look at the data and you may need to standardise a couple of things to make that work:

Academic Year has '_' and '-' in different tables, for example, 2023-2024. Choose one format and change the data.

You have Class in one table and Grade in the other. You might want to call them the same thing.

If you need to return a numeric value, that will be achievable but you will want to have a number in the Summary table too.  Just add a custom column in Power Query with the logic.  Alternatively, create a dimension table which matches  grade term (for example, Ruby) with numeric value.

That should be enough to get you started