Ayuda de DAX necesaria: ¿Cómo se agrupa por en una medida y se obtiene el máximo del grupo usando DAX?

[Syndicate_Admin]

Tengo una situación en la que tengo una mesa como esta:

Año	Nombre	Valor
2019	Instalación A	10
2019	Instalación B	20
2019	Instalación C	15
2020	Instalación B	5
2020	Instalación C	1
2021	Instalación A	2
2021	Instalación B	-25
2021	Instalación C	75
2018	Instalación A	10
2018	Instalación B	5
2018	Instalación C	18

Quiero que una persona pueda seleccionar "su ubicación" y ver los valores anuales promedio y los valores máximos para otras instalaciones (excluyendo la suya) de la siguiente manera:

Para hacer esto, necesito llegar a una medida que

1. Filtra un nombre específico (es decir, "Instalación B") que cambia dinámicamente en función de un filtro (por ejemplo, hoy filtro la Instalación B, mañana la Instalación C)
2. Grupos por año
3. Promedia los valores de ese año
4. Da el promedio máximo.

Aquí hay un ejemplo de cómo sería la salida en un formato de tabla (pero no es dinámica basada en filtros):

En este ejemplo, el MAX sería 38.5.

Pensé que podría hacer esto en una medida usando una variación en el código como este, pero no puedo llegar a una columna específica en el grupo:

Aquí está el DAX:

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

RETURN 

Max(Value1[AverageVal])

¿Alguien tiene ideas?

[Syndicate_Admin]

@bcdobbs, funciona maravillosamente! Mank gracias.

[Syndicate_Admin]

Acabo de pasar unos buenos 20 minutos rascándome la cabeza preguntándome por qué mi medida original no funcionó. Si está interesado, la siguiente corrección lo resuelve (Observe el cálculo adicional dentro de 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

Sin el CALCULATE adicional dentro del MAXX no obtienes una transición de contexto. Por lo tanto, en la fila total no hay ningún año en el contexto del filtro y, aunque esté iterando cada año en el MAXX, ese año no se está moviendo al contexto del filtro. Así que devuelve el promedio de todos los años

[Syndicate_Admin]

Vea si esto funciona para usted: Demo

Creé una tabla de dimensiones Facility y Year con el siguiente DAX:

Facility = DISTINCT ( ValueTable[Name] )

Year = DISTINCT ( ValueTable[Year] )

Y los usó para formar un esquema de estrellas:

Medida básica para el Mecanismo seleccionado:

Selected Average = AVERAGE ( ValueTable[Value] )

Luego utilicé MAXX para iterar a lo largo de los años y calcular un promedio para cada retorno máximo. El CALCULATE me permite invertir la selección:

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

[Syndicate_Admin]

@bcdobbs, gracias por la respuesta! Esto definitivamente está cerca. El único problema con el que me estoy encontrando es que esto funciona cuando se aplica a un año determinado, pero muestra el promedio "general" cuando no se le da un contexto anual. En esta imagen, puede ver que el Promedio en la tarjeta muestra el promedio de los promedios en lugar de 25, que es el valor máximo para todos los años.

¿Se puede modificar esto para mostrar el valor más grande?

[Syndicate_Admin · Accepted solution]

Lo sentimos, intente modificarlo de la siguiente manera:

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