How find Max of Average

[jehoppis]

I have a buch of employees that work each day of the week, and I would like to be able to compute the average hours worked by each employee, then find the max of that.

For example, say I have John, Mary, and Tyler as employees working as below.

Employee	Day	Hours
John	Mon	5
Mary	Mon	7
Tyler	Mon	3
John	Tue	5
Mary	Tue	4
Tyler	Tue	6
John	Wed	5
Mary	Wed	6
Tyler	Wed	9
John	Thu	5
Mary	Thu	3
Tyler	Thu	12
John	Fri	5
Mary	Fri	10
Tyler	Fri	5

Then I would like to get 

Employee	Avg Hours	Max Hours
John	5	7
Mary	6	7
Tyler	7	7

If I let Avg Hours = AVERAGE(Table[Hours]), then I get what I want in the second column of the second table, but I'm not sure how to get the third column. Any help would be greatly appreciated.

[djurecicK2]

@jehoppis  Please check your DAX vs what I posted earlier. You need to add [employee] or whatever field you are using for employee in the ALL statement.

[jehoppis]

Oh, thanks! Sorry, I think I read what you wrote, then watched the video where she didn't put in the column name inside of the ALL. Thanks so much! I will accept your answer.

[djurecicK2 · Accepted solution]

@jehoppis Here is how you can do it with measures.

AverageHours = AVERAGE('Table'[Hours])

Max Average Hours = MAXX(ALL('Table'[Employee]), [AverageHours])

I did not copy all of your data, so in this case 5.5 is the Max Average.

Please mark as accepted solution if this resolves the issue.

Credit to: https://www.youtube.com/watch?v=DAvzV-4Li7A

[jehoppis]

Hmm, this does not seem to resolve the issue. When I type as you say, I get this:

I believe the ALL is causing it to look at the entire table before computing the average of each group. I watched the video, and tried replicating her MAXX of COUNTROWS, but I get 1 for each entry if I do that. Not sure why.

Edit: Would you be able to post the data you used as well to get 5.5?

[aj1973]

HI @jehoppis 

The Max Hours you want to add, is it suppose to be dynamic or constant and equal to 7? 

Because for example John can't have Max Hours = 7

[jehoppis]

I should have labelled Max Hours as Max of Avg Hours instead. Since the Avg Hours column is 5, 6, 7, I want the Max column to be Max(5, 6, 7) = 7 for each row. The end goal is trying to normalize Avg Hours so that I have values between 0 and 1, so I would like to do (Avg - Min)/(Max - Min).

Edit: As a Calculate Table, this seems to work

Table 2 = SUMMARIZE('Table', 'Table'[Employee], "Average Hours", AVERAGE('Table'[Hours]))

Max Hours = MAXX('Table 2', 'Table 2'[Average Hours])

but I would like to do this using measures.