Average of max group by

[Jeremy19]

Hello, 

I have a data set like this :

Name    Var1   

A            5

A            7

B             5

B             2

C            1

C            3

I have to calculate the average of the max of each name. But I must have just the mean value like the table below :

Name   Var1    AvgMax

A           7            5

B           5            5

C           3            5

I need to have the same value per name to insert this measure into a graph which will have a line representing the group average.

Could you help me ?

[smpa01]

@Jeremy19  I have done it like this

maxval := CALCULATE(MAX('Table'[Column2]),ALLEXCEPT('Table','Table'[Column1]))
maxvalfilter := IF(MAX('Table'[Column2])=[maxval],[maxval])
summaxval := CALCULATE(SUMX('Table',[maxvalfilter]),ALL('Table'))
count := CALCULATE(CALCULATE(COUNTROWS('Table'), FILTER('Table','Table'[Column2]=[maxvalfilter])),ALL('Table'))
average := DIVIDE([summaxval],[count])

[Jeremy19]

Thanks for your help. But the solution does not take into account all the filters of the page like the group or the date.

In my actual dataset the expected value here is 1174

[smpa01]

@Jeremy19  I wish you included that in your original question

[amitchandak]

@Jeremy19 , Please find the attached file after the signature.

Measure = calculate(averageX(values('Table'[Name]), calculate(max('Table'[Var1]))),all('Table'))

[Jeremy19]

It works well on this example but when I add a filter by selecting two names it keeps the value 5

[Jeremy19]

I know sorry I thought that would not change the solution and I wanted to try to simplify the problem

[amitchandak]

@Jeremy19 , Try measures like

averageX(values(Table[Name]), calculate(max(Table[Var1])))

or

averageX(values(Table[Name]), calculate(max(Table[Var1]), allexcept(Table, Table[Name])))

[Jeremy19]

Thanks @amitchandak . These are solutions that I have already tested but I still get:

Name   Var1    AvgMax

A           7            7

B           5            5

C           3            3

[amitchandak · Accepted solution]

@Jeremy19 , Try like

calculate(averageX(values(Table[Name]), calculate(max(Table[Var1]))),all(Table))

[Jeremy19]

This time I have an identical value, but it is not the right result. This measure does not take into account different filters that I have such as the date or the positions of the players.

[Jeremy19]

Just with a small change to adapt to the filters it was the right solution

calculate(averageX(values(Table[Name]), calculate(max(Table[Var1]))),ALLEXCEPT(Table,Table[Poste]Table[Date]))

Thanks !