Sims, Attendance and Power Bi

[sefton]

Got myself a dashboard showing attendance across the MAT for this academic year, it's got date filters so you can see attendance across your chosen range and for sub-groups, it also includes all students who have been on roll for the current year so matches DFE figures, not just those current.

 

I'd like to show PA for the same range but I can't figure out how to do it. I can do a table showing the students and their % attendance and a Y/N measure for whether that are PA but I can't produce a whole cohort % from that. I can do a new table and copy the students and PA to that and it works but that is static whereas I want to be able to use the date filters.

 

The table I am using has

Student ID

Gender - for filtering

SEN Status Code -for filtering

Pupil Premium - for filtering

EAL - for filtering

Statistical Meaning

Mark

Mark Date

AM/PM

Current/Leaving Year

 

So students have multiple rows.

[bobsmith]

how about using the date filters to copy the entire table you start with but ONLY the rows that fall within the date range specified? Then you can use all your current filters for sub groups etc on the new derived table.

 

gotta admit it's a bit brute force but can't think of how else to do that.

[RootsSchImp]

Hi @sefton,

 

I guess you have a measure for your attendance right? It's important to use measures as they respond to slicers/filters on the page whereas calculated columns don't. So say your attendance measure is something like:

 

Attendance = SUMX(table,IF(OR(table[statistical Meaning] = "P",table[statistical Meaning] = "E"),1,0) / SUMX(table,IF(table[statistical Meaning] = "C",0,1)

 

SUMX iterates through the table performing the expression for each row of the table (that is visible after applying the selected filters), does the IF expression and sums the result - so this gives you the sum of all marks that are P or E divided by the sum of all marks that aren't Cancelled (ie the possible marks).

 

You can use another another SUMX to get PA like this:

 

PA = SUMX(VALUES(StudentID),IF(AND(ISNUMBER([Attendance],[Attendance]<.9),1,0) / SUMX(VALUES(StudentID),IF(ISNUMBER([Attendance]),1,0)

 

VALUES produces a table of all the values of a columns visible in the current filter context, so your numerator is each kid who has attendance below 90% and your denominator is kids who have at least one possible session in current filter context.

 

Let me know how you get on with this

 

Ed

[sefton]

I have done everything by measure.

The below give the numbers for each category based on the statistical meaning which is part of the export from Sims.

 

n_auth_abs = COUNTAX(FILTER(attendance,attendance[statistical meaning]="Authorised Absence"),Attendance[External Id])

n_present = COUNTAX(FILTER(attendance,attendance[statistical meaning]="Present"),attendance[External Id])+COUNTAX(FILTER(attendance,attendance[statistical meaning]="Approved Educational Activity"),attendance[External Id])

n_unauth_abs = COUNTAX(FILTER(attendance,attendance[statistical meaning]="Unauthorised Absence"),attendance[External Id])

 

Then I calculate the % with the following measure.

 

%att = [n_present]/([n_auth_abs]+[n_unauth_abs]+[n_present])

 

I'm then entering this for the calculated measure using your suggestion as a guide.

 

PA = SUMX(VALUES(Attendance[External Id]),IF(AND(ISNUMBER(Attendance[%att],Attendance[%att]<.9),1,0)/SUMX(VALUES(Attendance[External Id],IF(ISNUMBER(Attendance[%att]),1,0)

 

I'm getting back 'Too many arguments were passed to the ISNUMBER function. The maximum argument count for the function is 1.'

 

Cheers for your assistance.

 

Ian

[RootsSchImp]

Sorry - that was my over hasty typing. ISNUMBER takes one argument, so your formula is missing a close brackets after the first Attendance[%att]

 

should read

 

PA = SUMX(VALUES(Attendance[External Id]),IF(AND(ISNUMBER(Attendance[%att]),Attendance[%att]<.9),1,0)/SUMX(VALUES(Attendance[External Id],IF(ISNUMBER(Attendance[%att]),1,0)

[sefton]

Hi Ed,

 

Thanks for that, there were a couple of changes with the brackets, aren't there always with these formula, but it works great now.

 

PA = SUMX(VALUES(Attendance[External Id]),IF(AND(ISNUMBER(Attendance[%att]),Attendance[%att]<=.9),1,0))/SUMX(VALUES(Attendance[External Id]),IF(ISNUMBER(Attendance[%att]),1,0))

 

My next task is to whole year attendance correct by disregarding Year 11 in the last HT but that can wait until another day.

[sefton]

A bit of a follow up on this one.

 

I am measuring persistent absence for students in our school which is anybody with less than 90% attendance. The issue I have is that some are being returned blank. Now some are blank because they are educated at another establishment although they are on our roll. But a couple are blank because they are school refusers and so don't attend anywhere and I do need them to show as less than 90%. The DAX I use is the below.

 

 

 

%att = [n_present]/([n_auth_abs]+[n_unauth_abs]+[n_present])

 

 

Those I need to show, for example, as 0% would have 0/0+120+0 whereas those I need to stay blank would be 0/0+0+0 as they do not need to attend.

 

If I do the usual

 

 

 

%att = [n_present]/([n_auth_abs]+[n_unauth_abs]+[n_present])+0

 

 

then those who do not need to attend show as 0% and are then included when I calculate my persistently absent students.

Edited November 21, 2019 by sefton

[RootsSchImp]

Hi Sefton,

 

Distinguishing between no attendance and 0% is why I have ISNUMBER in the measure (0 returns true, blank returns false). Adding 0 to everybody will stop this from working though - did you do that to avoid a divide by 0 error?

 

If so try %att = DIVIDE([n_present],([n_auth_abs]+[n_unauth_abs]+[n_present]))

 

As that automatically disregards 0 denominators.

[bobsmith]

How does a school refuser come up as blank? Shouldn't they be getting continuous unauthorised absences?

 

As for the dual roll students - I include the "sessions possible" in my calculations to deal with them.

[sefton]

How does a school refuser come up as blank? Shouldn't they be getting continuous unauthorised absences?

 

As for the dual roll students - I include the "sessions possible" in my calculations to deal with them.

 

They are coming up as blanks when the total number of presents is 0, so the formula would be (as of yesterday 102 possible sessions) 0/0+102+0 which for my formula return a blank and not 0%.

 

Hi Ed,

 

Tried the formula you suggested and that is also returning a blank and not 0%

 

The issue with PA does not exist, it correlated with the two students we have who are non-attenders but when I went home last night I had a further look at it and our admin had made them a unique pastoral group for them so they could differentiate them, I explained the issues this could cause with the census etc and restored the students to the correct groups and after the rerunning the exports the PA % matches.

Edited November 22, 2019 by sefton

[bobsmith]

my code for percent attendance straight out of my powerbi report

 

Percentage Attendance YTD = iferror((Attendance[Present]/Attendance[Possible])*100, 0)

 

works fine for my dual roll kids - even those with 0 sessions possible this year.