Forum Discussion

tylerr2142's avatar
tylerr2142
New Member
5 years ago

Calculate Average for Common Columns in Matrix

I would like to calculate the average of the baseline assessment for just the common standards. In this case, it should be the average of 7.1, 7.2 and 7.3. I would also like to calculate (nonbaseline...
Need Help
tylerr2142's avatar
tylerr2142
New Member
5 years ago

amitchandak Anonymous 
The data structure looks like the below:

UsernameAttemptDateExamSectionScore
Sally1/1/2020Baseline1.150%
Sally1/1/2020Baseline1.225%
Sally1/1/2020Baseline1.3100%
Sally1/1/2020English 1011.175%
Sally1/1/2020English 1011.280%
Sally1/1/2020English 1011.490%
Sally1/1/2020English 1011.795%

 

I want to be able to calculate the improvement from Baseline exam to other Non-Baseline exams with identical sections. (I.E: a score of 50% for baseline section 1.1 to the 75% for English 101 section 1.1. 

 

Thanks for the help!

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Hi tylerr2142,

Did you mean you want to calculate the average 'Score' across multiple exams based on the most general 'Section' which existed all exams, right?

If that is the case, you can try to use the following measure expression to replace the scope field to use in matrix and confirm if it works on your side:

Measure = 
VAR currSelection =
    MAX ( 'Table'[Section] )
VAR _list =
    CALCULATETABLE (
        VALUES ( 'Table'[Section] ),
        FILTER (
            SUMMARIZE (
                ALLSELECTED ( 'Table' ),
                [Username],
                [AttemptDate],
                [Section],
                "cExam", COUNTA('Table'[Exam])
            ),
            [cExam] >= 2
        )
    )
RETURN
    IF (
        currSelection IN _list,
        CALCULATE (
            AVERAGE ( 'Table'[Score] ),
            ALLSELECTED ( 'Table' ),
            VALUES ( 'Table'[Username] ),
            VALUES ( 'Table'[AttemptDate] ),
            VALUES ( 'Table'[Section] )
        )
    )

Regards,
Xiaoxin Sheng