[sims] Power BI KS3 Assessment

[duesouth]

A couple of months ago, we started playing with Power BI. I've downloaded the resources from ESS and have created something in Power BI for attendance and behaviour. I'm self-taught with Power BI - or at least with the help of YouTube! SLT love it - and now want to put assessment in there.

 

At KS3, we have "Mastery Levels" - ranging from Emerging to Mastered. I've pulled out the Targets from SIMS (table has External ID, Result (i.e. the Target), Aspect and Subject) - and also the Mastery Levels from the end of Autumn term assessment (table has External ID, Result (i.e. the current level), Resultset, Aspect and Subject).

 

In Power BI, I've been able to create a table with:

 

Pupil Name, Mastery Level and Target - which links up to subject and class slicers perfectly.

 

My problem is creating a "+/- Target".

 

I've created a separate Mastery Levels table with a corresponding lookup value for each Mastery level:

 

Mastery Level Value

Emerging 1

Emerging+ 2

etc. etc.

 

However, I am really struggling to create a measure (or column) that is KS3 Target Lookup Value - Autumn Term Assessment Lookup Value. E.g. if a pupil has a target of Emerging+ (2) and current level is Emerging (1), then they would be -1.

 

Can this be done is Power BI? If so, does anyone have an example formula, or tell me if I need to change the structure of the model - i.e. Targets and End of Term Assessments in the same table. Or is it best to do the +/- Target calc in SIMS or a spreadsheet first, and bring that into Power BI?

 

Thanks in advance.

[RootsSchImp]

There's lots of ways you can do this but I think the simplest is:

 

Combine your two results tables so you have results and targets in the same table. You do this is Power Query using 'Append Query'.

Still in power query, join your mastery levels table using 'Merge Query' so you have the numerical value of each assessment on the main table (make sure the result type for this value is numerical).

 

OK, so let's say you now have one table called 'Results' and in its Aspect column the value is either 'Current' or 'Target'.

 

Your measure would be: Target Difference = CALCULATE( AVERAGE(Results[Points]),Results[Aspect]="Current") - CALCULATE( AVERAGE(Results[Points]),Results[Aspect]="Target")

[duesouth]

Thank you so much. I would have never thought of merging in the mastery levels table like that!