Calculating Average

[WJ876400]

Hello

I have a dataset of students and the units they are undertaking and then the grade for that unit. What i want to achieve is an average mark for the students class, so the unit will be repeated each year and I need to single out the average for the class for that year. I have done a rough idea of the data below, you can see the unit Test 1 runs over 3 years but I need to know the average mark for each year. Each student has their own ID and the unit has its own ID for each year.

Unit Name	Year	Grade	ID
Test 1	19/20	50	123
Test 1	20/21	60	234
Test 1	21/22	70	456
Test 1	21/22	71	888
Test 1	21/22	75	999
Test 2	21/22	91	567
Test 2	21/22	25	111
Test 2	21/22	65	222
Test 3	21/22	70	891
Test 4	21/22	82	910

hopefully that makes sens, any help is appreciated, thanks

[v-zhangti · Accepted solution]

Hi, @WJ876400 

You can try the following methods.

Column:

Column =
CALCULATE (
    AVERAGE ( 'Table'[Grade] ),
    FILTER (
        'Table',
        [Year] = EARLIER ( 'Table'[Year] )
            && [Unit Name] = EARLIER ( 'Table'[Unit Name] )
    )
)

Measure:

Measure =
CALCULATE (
    AVERAGE ( 'Table'[Grade] ),
    ALLEXCEPT ( 'Table', 'Table'[Year], 'Table'[Unit Name] )
)

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@WJ876400 , Sone of the two

AverageX(Values(Table[Year]) , Calculate(Sum(Table[Grade])) )

or

AverageX(Summarize(Table,Table[Year], Table[Unit Name]) , Calculate(Sum(Table[Grade])) )

[WJ876400]

Thank you for the response, sorry do I do this in the query editor or add it as a new measure or column?

thanks