Forum Discussion

jblackburnHWGA's avatar
jblackburnHWGA
Icon for Helper I rankHelper I
4 years ago
Solved

Help with a measure please - attendance data

I'm trying to get a stacked graph that shows the number of persistant absentees at my academy. A persistant absentee is someone who has less than 90% attendance.

 

I've done it on an old report but i had different data. The old data had an attendance column that came as a %. I just made a custom column that gave a yes or no depending on the attendance column % figure.

I now have data that shows daily attendance as a mark twice a day. The change is due to an manage system change. This new data is better it lets me go back over weeks, years etc. I've used a measure to get an attendace % and a measure to show the % of persistant absence per person but i can't figure out how to just have a persistant absentee of yes or no. If they're overall average attendance is less than 90% they are classed as a persistant absentee.

This is my attendance data

 

 

my attendance measure is

Attendance =
VAR Present = SUM(ATTENDANCE[IS_PRESENT])
VAR Possible = SUM(ATTENDANCE[IS_POSSIBLE_ATTENDANCE])
RETURN
DIVIDE(Present,Possible)
 
my persistant adsence measure is
 
Persistent Absence =
VAR PAstds = SUMX(STUDENTS,IF(
AND([Attendance]<.9,ISNUMBER([Attendance])),
1))
VAR TotalStds = SUMX(STUDENTS,IF(
ISNUMBER([Attendance]),
1))
RETURN
PAstds/TotalStds
 
My solution is a play on the persistant absentee measure. My brain just can't make the jump.

 

 

Need Help
  • DataInsights's avatar
    DataInsights
    4 years ago

    jblackburnHWGA,

     

    Try this measure. It sums IS_PRESENT and IS_POSSIBLE_ATTENDANCE for each student, and counts 1 if the Percent is less than .9.

     

    Persistent Absence =
SUMX (
    STUDENTS,
    VAR Present =
        CALCULATE ( SUM ( ATTENDANCE[IS_PRESENT] ) )
    VAR Possible =
        CALCULATE ( SUM ( ATTENDANCE[IS_POSSIBLE_ATTENDANCE] ) )
    VAR Percent =
        DIVIDE ( Present, Possible )
    RETURN
        IF ( Percent < .9, 1 )
)

     

5 Replies

  • DataInsights's avatar
    DataInsights
    Icon for Super User rankSuper User
    4 years ago

    jblackburnHWGA,

     

    Try this measure. It sums IS_PRESENT and IS_POSSIBLE_ATTENDANCE for each student, and counts 1 if the Percent is less than .9.

     

    Persistent Absence =
SUMX (
    STUDENTS,
    VAR Present =
        CALCULATE ( SUM ( ATTENDANCE[IS_PRESENT] ) )
    VAR Possible =
        CALCULATE ( SUM ( ATTENDANCE[IS_POSSIBLE_ATTENDANCE] ) )
    VAR Percent =
        DIVIDE ( Present, Possible )
    RETURN
        IF ( Percent < .9, 1 )
)

     

    • jblackburnHWGA's avatar
      jblackburnHWGA
      Icon for Helper I rankHelper I
      4 years ago

      Percent is a reserved word 🙂 all working on the measure i get a 1 next to a persistant absentee now.

      How do you make the measure work as a legend for the table? I want to show it in a stacked bar chart. The only way i know how to do that is by using a column for it.

       

      I thought on the measure i could have 

       

      RETURN
      IF ( Per <= .9, "Y" else "N" ) 
       
      But its doesn't like else