How do I calculate an average of one column by filtering it through two other columns

[SebTsang]

I am trying to do an average calculation of the count per location per grade, please help.

[Anonymous · Accepted solution]

Hi @SebTsang 

Try this code to build a calculated column.

Count Average Per Location and Grade =
CALCULATE (
    AVERAGE ( 'Sample'[Count] ),
    FILTER (
        'Sample',
        'Sample'[Location] = EARLIER ( 'Sample'[Location] )
            && 'Sample'[Grade] = EARLIER ( 'Sample'[Grade] )
    )
)

Result is as below.

If you want a measure, you can try this code.

Measure = 
CALCULATE (
    AVERAGE ( 'Sample'[Count] ),
    FILTER (
        ALL('Sample'),
        'Sample'[Location] = MAX ( 'Sample'[Location] )
            && 'Sample'[Grade] = MAX ( 'Sample'[Grade] )
    )
)

Result is as below.

Best Regards,
Rico Zhou

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

[MFelix]

Hi @SebTsang ,

The best way is to use a measure just AVERAGE(Table[Column]) and then place the grades and the locaiton on your viusalization no need to create an additional column on your model.