Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Summarizing and Combing Tables

I'm struggling with combining and summarizing tables. I have 3 related tables. A student table, containing basic student information; a center table which provides the center name, and an attendance table which provides daily student attendance data. The end goal is to: (1) calculate the number of possible attendance days per month per center, (2) calculate the number of days absent per student per month, and (3) combine (1) and (2) to calculate each student's monthly absentee percentage. Below is a sample of the 3 raw data tables and the desired end-result. I'm envisioning doing this by creating calculated tables, but perhaps there is a more effecient method?

 

 

 

Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    6 years ago

    Hi Anonymous 

    Create a column in "Attendance" table

    month-year = FORMAT(Attendance[AttendanceDate],"yyyy-mm")

     

    Create measures in "Attendance" table

    CountOfAbsenses =
CALCULATE (
    DISTINCTCOUNT ( Attendance[AttendanceDate] ),
    FILTER (
        ALL ( Attendance ),
        Attendance[StudentID] = MAX ( Attendance[StudentID] )
            && Attendance[Student_Attendance] IN { "Absent (Excused)", "No Show" }
    )
)

CountOfAttendance =
CALCULATE (
    DISTINCTCOUNT ( Attendance[AttendanceDate] ),
    FILTER (
        ALL ( Attendance ),
        Attendance[StudentID] = MAX ( Attendance[StudentID] )
            && Attendance[Student_Attendance] <> "Service Day"
    )
)

flag =
IF (
    MAX ( Student[StudentID] ) <> BLANK ()
        && MAX ( Student[ProgramType] ) <> "After Hours"
        && MAX ( Center[CenterID] ) <> BLANK ()
        && MAX ( Attendance[month-year] ) <> BLANK (),
    1,
    0
)


% =
IF (
    [CountOfAbsenses] / [CountOfAttendance]
        = BLANK (),
    "0%",
    [CountOfAbsenses] / [CountOfAttendance]
)

    Best Regards
    Maggie

     

    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    7 years ago

    Can you post that last table as text? That's a lot of information to type in by hand.

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi Greg,

      Sure. I'm attaching the 3 raw data tables, as well as the end-result table.

       

      Table - Center
      CenterIDCenterName
      3001Baker County
      3002Columbia County

       

      Table-Student  
      CenterIDStudentIDStudentNameProgramType
      3001101Jane DoeDay
      3001102Jim WeberDay
      3001103Ken AdamsAfter Hours
      3002104Abigail StevensDay
      3002105Tenecia JenkinsDay

       

      Table-Attendance  
      StudentIDAttendanceDateServiceTypeStudent_Attendance
      1015/1/2019Daily AttendanceAttended
      1015/1/2019LunchAttended
      1025/1/2019Daily AttendanceAbsent (Excused)
      1025/1/2019LunchAbsent (Excused)
      1035/1/2019Daily AttendanceAttended
      1045/1/2019Daily AttendanceAttended
      1045/1/2019LunchAttended
      1055/1/2019Daily AttendanceNo Show
      1055/1/2019LunchNo Show
          
      1015/5/2019Daily AttendanceAttended
      1015/5/2019LunchAttended
      1025/5/2019Daily AttendanceAttended
      1025/5/2019LunchAttended
      1035/5/2019Daily AttendanceAttended
      1045/5/2019Daily AttendanceAttended
      1045/5/2019LunchAttended
      1055/5/2019Daily AttendanceAttended
      1055/5/2019LunchAttended
          
      1015/10/2019Daily AttendanceAbsent (Excused)
      1015/10/2019LunchAbsent (Excused)
      1025/10/2019Daily AttendanceAbsent (Excused)
      1025/10/2019LunchAbsent (Excused)
      1035/10/2019Daily AttendanceAttended
      1045/10/2019Daily AttendanceService Day
      1055/10/2019Daily AttendanceService Day

       

      StudentIDStudentNameCenterIDCenterNameMonth-YearCountOfAbsensesCountOfAttendanceDatesAbsenteePerc
      101Jane Doe3001Baker County2019-051333%
      102Jim Weber3001Baker County2019-052367%
      104Abigail Stevens3002Columbia County2019-05020%
      105Tenecia Jenkins3002Columbia County2019-051250%
      • v-juanli-msft's avatar
        v-juanli-msft
        Community Support
        6 years ago

        Hi Anonymous 

        Create a column in "Attendance" table

        month-year = FORMAT(Attendance[AttendanceDate],"yyyy-mm")

         

        Create measures in "Attendance" table

        CountOfAbsenses =
CALCULATE (
    DISTINCTCOUNT ( Attendance[AttendanceDate] ),
    FILTER (
        ALL ( Attendance ),
        Attendance[StudentID] = MAX ( Attendance[StudentID] )
            && Attendance[Student_Attendance] IN { "Absent (Excused)", "No Show" }
    )
)

CountOfAttendance =
CALCULATE (
    DISTINCTCOUNT ( Attendance[AttendanceDate] ),
    FILTER (
        ALL ( Attendance ),
        Attendance[StudentID] = MAX ( Attendance[StudentID] )
            && Attendance[Student_Attendance] <> "Service Day"
    )
)

flag =
IF (
    MAX ( Student[StudentID] ) <> BLANK ()
        && MAX ( Student[ProgramType] ) <> "After Hours"
        && MAX ( Center[CenterID] ) <> BLANK ()
        && MAX ( Attendance[month-year] ) <> BLANK (),
    1,
    0
)


% =
IF (
    [CountOfAbsenses] / [CountOfAttendance]
        = BLANK (),
    "0%",
    [CountOfAbsenses] / [CountOfAttendance]
)

        Best Regards
        Maggie

         

        Community Support Team _ Maggie Li
        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.