DAX Help Needed: How do you group by in a measure and get the max of the group using DAX?

[arpost]

I have a situation where I have a table like this:

Year	Name	Value
2019	Facility A	10
2019	Facility B	20
2019	Facility C	15
2020	Facility B	5
2020	Facility C	1
2021	Facility A	2
2021	Facility B	-25
2021	Facility C	75
2018	Facility A	10
2018	Facility B	5
2018	Facility C	18

I want to make it so that a person can select "their location" and see the average yearly values and max values for other facilities (excluding their own) like so:

To do this, I need to come up with a measure that

1. Filters out a specific name (i.e., "Facility B") that changes dynamically based on a filter (e.g., today I filter out Facility B, tomorrow Facility C)
2. Groups by year
3. Averages the values for that year
4. Gives the max average.

Here is an example of what the output would be like in a table format (but isn't dynamic based on filters):

In this example, the MAX would be 38.5.

I thought I could do this in a measure using a variation on code like this, but I can't get to a specific column in the group:

Here's the DAX:

MaxNonSelectedFacility = 
var Value1 = GroupBY(Filter('Table','Table'[Name] <> SelectedValue(Facilities[Name]) ),'Table'[Year],"AverageVal",Average('Table'[Val]))

RETURN 

Max(Value1[AverageVal])

Anyone have ideas?

[arpost]

@bcdobbs, works beautifully! Mank thanks.

[bcdobbs]

I've just spent a good 20 minutes scratching my head wondering why my original measure didn't work. If you're interested the following correction sorts it out (Notice the extra calculate inside MAXX)

Max Average for Others Wrong = 

VAR SelectedFacility = SELECTEDVALUE ( Facility[Name] )

VAR Result =
    CALCULATE (
        MAXX (
            VALUES ('Year'[Year] ),
            CALCULATE ( AVERAGE ( ValueTable[Value] ) )
        ),
    REMOVEFILTERS( Facility ),
    NOT Facility[Name] = SelectedFacility
    )

RETURN Result

Without the extra CALCULATE inside the MAXX you don't get a context transition. So on the total row there is no year in the filter context and even though you're iterating over each year in the MAXX that year isn't being moved into the filter context. So it returns the average of all the years

[bcdobbs]

See if this works for you: Demo 

I created a Facility and a Year dimension table with the following DAX:

Facility = DISTINCT ( ValueTable[Name] )

Year = DISTINCT ( ValueTable[Year] )

And used these to form a star schema:

Basic measure for the selected Facility:

Selected Average = AVERAGE ( ValueTable[Value] )

I then used MAXX to iterate over the years and calculate an average for each return the max. The CALCULATE allows me to reverse the selection:

Max Average for Others = 

VAR SelectedFacility = SELECTEDVALUE ( Facility[Name] )

VAR Result =
    CALCULATE (
        MAXX (
            VALUES ('Year'[Year] ),
            AVERAGE ( ValueTable[Value] )
        ),
    REMOVEFILTERS( Facility ),
    NOT Facility[Name] = SelectedFacility
    )

RETURN Result

[arpost]

@bcdobbs, thanks for the reply! This is definitely close. The one issue I am running into is that this works when scoped to a given year but shows the "overall" average when not given a year context. In this image, you can see that the Avg. in the card is showing the average of the averages instead of 25, which is the max value for all the years.

Can this be modified to show the largest value?

[bcdobbs · Accepted solution]

Sorry, try modifying it as follows:

Max Average for Others = 

VAR SelectedFacility = SELECTEDVALUE ( Facility[Name] )

VAR Result =
        MAXX (
            VALUES( 'Year'[Year] ),
            CALCULATE (
                AVERAGE ( ValueTable[Value] ),
                REMOVEFILTERS( Facility ),          
                NOT Facility[Name] = SelectedFacility
            )
        )

RETURN Result