topic Calculate benchmark average with filter by group in DAX Commands and Tips

Calculate benchmark average with filter by group

JFG1234 — Fri, 20 Jan 2023 02:00:27 GMT

I am trying to add a field called "Benchmark" to the visual below (left).

The "Benchmark" field needs to be separated by Sex as can be seen in the right table (female = 11, male = 11.88).

The final output should be the first left table with "Benchmark" added; when female = 11 and when male 11.88.

When I add the field I have created it expands out the table and creates a series of empty records (i.e. Count of Year with blank values), but gets the benchmark correct.

This is the calculation I am using the calculate the benchmark. It should take the average of performance "Points" where the "Rank" is = 3 and account for Sex.

This is the model schema.

Re: Calculate benchmark average with filter by group

amitchandak — Fri, 20 Jan 2023 02:29:18 GMT

JFG1234 , Try Measure like

calculate(Average(Table[Points]), filter(allselected(Table) , Table[Rank] = 3 && Table[Sex]=max(Table[Sex])))

Re: Calculate benchmark average with filter by group

JFG1234 — Fri, 20 Jan 2023 03:14:55 GMT

Sex isn't in the same table as Points, it is connected by a many:1 relationship through the Athlete_ID as the last picture shows. Is it possible to do through the relationship?