Including missing records in a cumulative matrix visualisation

[maxbradley]

Not sure how to approach this problem, and looking for perspectives please!

I work for a school trust. I want to create a matrix visual with demographic groups on the Y axis and week beginning dates along the X.

The idea is that the values will show the attendance of the school as it was at these dates, that is, cumulatively. The issue I'm running into is that the data export I have from our MIS is like this. It returns the number of possible sessions a student could have attended, and the number of actual sessions they attended. By dividing one by the other we get their attendance. You can see that I've added a CumPoss and CumPres column, the formula of these is;

CumPresEA =

var std=

[SchoolStudentId]

var acaweek=

VALUE([AcaWeek])

var acayear=

VALUE([AcademicYear])

var cumpresea =

CALCULATE(

// Total count of PEA marks

SUM(

AttendanceSummaryByWeek[PresentAndEducationalActivityCount]

),

filter(

ALL(AttendanceSummaryByWeek),

// For that student and academic year, where the week is less than or equal to that week

AttendanceSummaryByWeek[SchoolStudentId]=std&&

AttendanceSummaryByWeek[AcademicYear]=acayear&&

AttendanceSummaryByWeek[AcaWeek]<=acaweek

)

)

return

IF(

ISBLANK(

cumpresea

),

0,

cumpresea

)

The issue is that once a student leaves, like student B did in the example data linked above, they no longer generate records in this weekly attendance export. A schools attendance is calculated as the total number of presents divided by the total number of possibles. This means that when a student leaves, their attendance continues to contribute to the overall attendance, weighted for the period of time they were at the school. However, in the example data linked above, student B stops attending, and so no longer gets rows for weekly attendance from the data source. Therefore, the cumulative rows don't generate values, and my matrix does not include these students attendance in the cumulative attendance for that week.

I haven't been able to generate a measure that would calculate this figure, as the week beginning date at the top of the matrix filters out the students from the measure anyway. I could possibly create 39 measures for each school week, but I'm hoping there's a better solution than that? The other alternative would be to insert records into the exported data for the leaving students, but I don't believe this is possible?

[v-kkf-msft]

Hi @maxbradley ,

If you want to include the value of student B in your calculations, please try using CROSSJOIN ( ALLSELECTED ( Sheet2[Student] ) , VALUES ( Sheet2[WeekStartDate] ) ) in the iterator functions, like this. 

Contain B Measure = 
IF (
    ISFILTERED ( Sheet2[Student] ),
    [Measure],
    AVERAGEX (
        ADDCOLUMNS (
            CROSSJOIN ( ALLSELECTED ( Sheet2[Student] ), VALUES ( Sheet2[WeekStartDate] ) ),
            "_CumPres", [Measure] + 0
        ),
        [_CumPres]
    )
)

 In the total section, this formula calculates rows that do not exist in the original data as 0 and then averages them.

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[maxbradley · Accepted solution]

Hi @v-kkf-msft,

Thanks for your help. I think your solution would have worked if the Y axis was just going to be individual students, but it is actually demographic groups of students belonging to certain categories. I think this meant that the ALLSELECTED function was therefore getting around the demographic groups filter. What I've done and has worked is use part of the code you suggested to create a seperate table like this

CumPresTbl = 
CROSSJOIN(
    // removing the filters from students
    ALLSELECTED(
        AttendanceSummaryByWeek[SchoolStudentId]
    ),
    // and distinct weekstartdate values, duplicates removed
    VALUES (
        AttendanceSummaryByWeek[WeekStartDate]
    )
)

This created student/weekdate combinations for weeks the student wasn't present for. I could then add a calculated column that summed their cumulative attendance, and was present in the totals. This is now showing the correct figures.

Thanks for your advice!