Forum Discussion

maxbradley's avatar
maxbradley
Frequent Visitor
4 years ago
Solved

Including missing records in a cumulative matrix visualisation

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...
Need Help
  • maxbradley's avatar
    maxbradley
    4 years ago

    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!

v-kkf-msft's avatar
v-kkf-msft
Community Support
4 years ago

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's avatar
maxbradley
Frequent Visitor
4 years ago

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!