Average Calculation With Filter

[KSASSESSMENT]

Hi

Wonder if anyone can help.

I have a set of results. Basically column A has a list of people, Column B has a list of test names and Column C has a list of scores.

I am trying to write a measure that will allow an average score to be shown across all selected test names.

The issue I am having at the moment is that the average stays the same even after applying a filter.

Average of Score total for Score =
CALCULATE(AVERAGE('Results'[Score]), ALLSELECTED('Results'[Test Name]))

It is showing an average across all test names and if I filter to a specific test name the average score stays the same whereas I would like it to show the average score for that test name.

I have a tried so many different options and just can't get this one right.

Full Name	Test Name	Score
Lisa Taylor	Test 1	67
Mark Taylor	Test 2	87
Sam Hughes	Test 1	76
Sam Taylor	Test 2	81

Can anyone point me in the right direction?

Regards

Lisa

[V-pazhen-msft · Accepted solution]

@KSASSESSMENT 

Allselected is not the right function.

https://docs.microsoft.com/en-us/dax/allselected-function-dax

AverageAllTestname = CALCULATE(AVERAGE(Result[Score]),ALL(Result))

AverageByTestname = CALCULATE(AVERAGE(Result[Score]),ALLEXCEPT(Result,Result[Test Name]))

Paul,
Best

[az38]

Hi @KSASSESSMENT 

try 

Average of Score total for Score = 
CALCULATE(AVERAGE('Results'[Score]); ALLEXCEPT('Results';'Results'[Test Name]))

ALLSELECTED removes filter from column

ALLEXCEPT removes filters from other column

do not hesitate to give a kudo to useful posts and mark solutions as solution