Working out percentage from a measure in a related table

[SPaine86]

Hi there!

I am trying to work out the percentage of pupils who have attendance at, or below, 90% using a measure.

The attendance table ('Attendance list') looks like this:

UID	Year	Term	Present + AEA	Possible
PupilA123	2021	Term 1	61	65
PupilA123	2021	Term 2	59	65
PupilA234	2021	Term 1	61	65
PupilA234	2021	Term 2	65	65
PupilB125	2021	Term 1	41	65

It is filtered in Power Query so that no pupil has null or 0 Possible attendance.

I have a corresponding table called 'Pupils', which has a one to many relationship with the Attendance List (each pupil can have multiple entries in Attendance list). I also have a table called 'Pupil Characteristics', which has a many to one relationship with Pupils (each pupil can have multiple characteristics) with the filter direction going both ways.

The measure I've created doesn't seem to work as I anticipated. I need it to:

• For each pupil, work out their attendance (by dividing the sum of [Present + AEA] by the sum of [Possible]), which I have achieved using a measure called [Attendance 2020/21]
• Filter the pupils who have a result less than or equal to 0.9, and count the number of rows
• Divide this number by the number of pupils for who we have an attendance record

This is my measure:

PA 2021 =

if(isblank(COUNTROWS (

FILTER (

Pupils,

VAR vResults = RELATEDTABLE('Attendance list') RETURN

COUNTROWS (

FILTER (vResults, [Attendance (2020/21)]<=0.9)

) >0

)

)

), 0,

divide( 

COUNTROWS (

FILTER (

Pupils,

VAR vResults = RELATEDTABLE('Attendance list') RETURN

COUNTROWS (

FILTER (vResults, [Attendance (2020/21)]<=0.9)

) >0

)

),

distinctcount('Attendance list'[UID]), 0))

This measure is counting someone as persistently absent (i.e. <=90% attendance) if they have at least one incidence of being PA in a term, but I want it to determine whether they are PA by looking at the sum of whatever data is included in the filter. For example, if we are looking at Term 1, Term 2, and Term 3, I was expecting this measure to work out the total Present + AEA and divide this by the Total Possible. Instead, it seems to be working out each term separately - and if one comes back as true then the pupil is included in the filer. My matrix to test the measure looks like this:

Name, Attendance [2020/21], PA

Bob Smith, 92%, 100%

Jack Jones, 91%, 0%

Drill down>>

Name, Attendance [2020/21], PA

Bob Smith, 92%, 100%

Term 1, 100%, 0%
Term 2, 100%, 0%
Term 3, 95%, 0%
Term 4, 90%, 100%

Can anyone see what I've done wrong?

Thank you in anticipation!

[amitchandak · Accepted solution]

@SPaine86 , try like

countx(filter(summarize(table,table[UID], table[Year], "_1",divide(sum(Table[Present + AEA]),sum(Table[Possible]))),[_1]>.9),[UID])

measure =
var _tab = filter(summarize(table,table[UID], table[Year], "_1",divide(sum(Table[Present + AEA]),sum(Table[Possible]))),[_1]>.9)
return
countx(summarize(_tab,[UID]),[UID])

[SPaine86]

Thank you, I did a minor tweaks to the code (e.g. >.9 is <=0.9) and it seems to be working nicely!

I didn't realise you could create a virtual table like this. Opens up a world of possibility...

Thank you again 🙂