PowerBI - Help Needed

[CyBeRkId2002]

Afternoon all,

 

I am hoping to get a little help and support from some of the experienced data analysts on here, on how I am best structuring data for PowerBI (@RootsSchImp, @elJukes etc.). My query is quite niche and for someone not experienced in data analytics quite hard to explain so please bear with me!

 

I have been tasked with a post-Assessment Point tracking sheet which on the surface sounds (and I think should be!) really straight forward, but when I have tried to create it I have struggled a little. I have attached a mockup of what I would like the sheet to look like. I have also attached a sample of how the assessment data is currently structured. What staff really want is to see, for their subject, which students are not on target at a particular AP and in conjunction with Attitude to Learning grades identify key students for interventions.

 

Mockup

 

 

Data Structure

 

 

My main questions I suppose are:

 

• Having played around a little I think I am best using a Matrix visualisation for this? Would that be a good starting point? I was thinking Name, followed by Subject for the rows and Term for the Columns.
  
• Am I right in thinking to get a matrix visualisation to work I would need to Pivot the Assessment Type column, using Result as the Values? This would, for example, give me a column for all of my different assessment types that I could add to the Values Column? So far so good and I can get it to work to a point and I can show Current TA and Attitude to Learning grades using this method.
  
• The problem I have is getting my calculated column to work. The Target Grade is in its own term (TTG) so it doesn't show alongside the AP1 data, for example, and therefore a calculated difference column showing students who are on target TA - TTG, for example) will not work). I am not a fan of entering data against every term unnecessarily so wondered if anyone could see a way of manipulating the data so I could calculate each AP against a single target value
  
• I would quite like to also also list students KS2 Banding. Again this is in a result set all on it's own. Even if we did copy this to all AP points, I would not really want it duplicating multiple times in the table as a matrix table would do.
  

 

Hopefully I have explained thoroughly enough that you can work out what I am trying to acheive but happy to explain further if anyone has some time to point me in the right direction. Please do tell me if I am going about this the wrong way, if I should be trying a different approach or giving up on PowerBI for this altogether.

Edited September 30, 2019 by CyBeRkId2002

[bfelton]

Do you calculate whether the student is on target or not in your MIS? I have this calculated in our SIMS marksheets in the background so I can then refer to this in PowerBI, the added benefit to this is the result is Keystage agnostic making analysis a lot easier.

[bobsmith]

• The problem I have is getting my calculated column to work. The Target Grade is in its own term (TTG) so it doesn't show alongside the AP1 data, for example, and therefore a calculated difference column showing students who are on target TA - TTG, for example) will not work). I am not a fan of entering data against every term unnecessarily so wondered if anyone could see a way of manipulating the data so I could calculate each AP against a single target value
  

Since you're pulling stuff to powerBI you can just link the tables as you see fit, so result sets shouldn't cause any issues at all, this is one of the main reasons to use PowerBI in the first place.

 

• I would quite like to also also list students KS2 Banding. Again this is in a result set all on it's own. Even if we did copy this to all AP points, I would not really want it duplicating multiple times in the table as a matrix table would do.
  

 

As above.

[CyBeRkId2002]

@bfelton - honest answer is I am not sure if our data manager does this at present and, if so, whether this exports to PowerBI. How do you have it in Sims? Another aspect altogether that shows whether the student is on target (and/or by how much?

@bobsmith - Yep, agree completely that the point of BI is the flexibility and the ability to manipulate to your needs. my question is around the specifics of how I would manipulate the date in this scenario... very much a BI beginner and looking for some practical advice given the scenario I have

[bfelton]

Yes it is in a separate aspect along with another that stores the numerical difference, both are very useful to use in SIMS and also PowerBI.

[CyBeRkId2002]

cheers! trying to work out the implications of this for us and it's usefulness.

 

• If I am understanding correctly, alongside our data assessment tracker we would have at least 1 other column on our marksheet called something along the lines of 'Target Difference'
  
• This new result would be held as an AP1/AP2/AP3 etc. result so would show on the matrix grid correctly
  
• If we went down this route can this column be retrospectively added and calculated for all results currently entered, or would this usually be something generated as a grade is input?
  

 

I suppose this is just a different way of doing the calculations, rather than in BI

[RootsSchImp]

HI @CyBeRkId2002

 

I feel your pain as (to the best of my knowledge) there isn't a great way to pivot some values in a matrix but not others. My preferred way round this (tho it involves a bit more legwork) is not to define a column value for the matrix, just a load of measures as values (each measure appearing once as a column in the matrix). This means you have to have separate measures for each of your assessment points.

 

If you go for CALCULATE(SELECTEDVALUE(,"#ERR"),[assessmenttype] = "Current",[term]="AP1") that will give you your current grade. The advantaged of using selected value is that it only returns a result if a single value is specified by the row/filter context (in this case these will be the student and subject from the matrix rows and the AT and term from the formula. If a kid has more than one current grade for a subject and term it will return #ERR.

 

You can use a similar measure to get target difference:

CALCULATE(SELECTEDVALUE(),[assessmenttype] = "Current",[term]="AP1") - CALCULATE(SELECTEDVALUE(,"#ERR"),[assessmenttype] = "target",[term]="TTG")

 

You'll want to add a check for missing targets/current grades though so add an if statement checking either measure for NULL values, return null (BLANK()) if yes, carry on with the calculation if no.:

 

IF(OR(ISBLANK(CALCULATE(SELECTEDVALUE(),[assessmenttype] = "Current",[term]="AP1"),ISBLANK(CALCULATE(SELECTEDVALUE(,"#ERR"),[assessmenttype] = "target",[term]="TTG")),

BLANK(),

CALCULATE(SELECTEDVALUE(),[assessmenttype] = "Current",[term]="AP1") - CALCULATE(SELECTEDVALUE(,"#ERR"),[assessmenttype] = "target",[term]="TTG"))

[bobsmith]

ok, so it's a relational database at heart, so you'd extract all the aspects you need (against their resultsets if needed). If this has to be in separate reports then that's fine.

 

the magic bit is that you're gonna need a unique primary key (so one of admission number/sims person id/upn depending on personal preferences), this will then allow you to link the various results into a new table (query) that exists only within powerbi.

 

Then I report on that resultant table - comparisons become a doddle.

[CyBeRkId2002]

@RootsSchImp - Thanks for the thoroughly detailed response. It pretty much detailed exactly what I was wanting to achieve without knowing exactly how (I am slowly starting to get my head around when to use FILTER, when to use CALCULATE etc.)!

 

The data is looking far more like I am envisioning now.

 

The only problem I would still like to solve is how I can add filters to this so I can give staff the ability to just show an AP at a time rather than inundating them with data! Obviously each one of these results is now stored against each Term so adding columns and / or slicers will not work. What would be the best approach for this?

 

Once again thanks very much for all of your help oin this, it is really appreciated.

[RootsSchImp]

Happy to BI of service

 

If you want to show just one AP and have it selected by slicer you can do that by replacing [term]="ap1" in your measure with [term]=selected value([term],"ap1")

 

Then if you have a slicer for [term] the measure will show the values for the selected term, with ap1 as the default if no term is selected.

 

If you want to get fancy you can replace "ap1" with calculate (selectedvalue(term),resultdate=max(resultdate)) which should make the default the term with the latest result.

 

If you make the clause a separate measure ('selectedterm') you can put that in the results measures (eg Term=[selectedterm] but also have it in a card to show staff which term's data is being displayed