Replicando TRIMMEAN en DAX

Hola, actualmente ejecuto una tabla dinámica que muestra valores agregados por día (columnas) y categoría (filas). Luego uso la función TRIMMEAN en Excel en las filas (días) para eliminar el 20% superior e inferior de los valores (los valores atípicos) y calcular el promedio del resto. Esto me da un valor único para cada fila (categoría) al que luego hago referencia en Power BI para generar un mapa de árbol.

Me gustaría omitir la necesidad de dividir los días y aplicarles la función TRIMMEAN. Sin embargo, DAX no parece tener esta funcionalidad integrada. Estoy tratando de replicarla en DAX sin mucho éxito.

He creado las siguientes medidas:

Valor Suma = suma (Base de datos[Test Value])

Valor máximo del día =maxx(valores(Base de datos[Day of Test]),Base de datos[Value Sum])

Valor mínimo del día = minx(valores(Base de datos[Day of Test]),Base de datos[Value Sum])

Todos esos funcionan bien. Sin embargo, cuando trato de combinarlos en una fórmula que calcula un promedio de valores excluyendo los valores atípicos, solo obtengo espacios en blanco. Funciona bien si codifico un límite, pero no si trato de extraer uno de la base de datos.

Para simplificar, la siguiente fórmula solo tiene el límite inferior (valor mínimo + 20%). ¿Alguna idea de lo que no funciona?

=
PROMEDIOX (
FILTRO (
VALORES ( Base de datos[Day of Test] ),
Base de datos[Value Sum]
> Base de datos[Min Day Value] * 1.2
),
Cascadas[Response Sum]
)

Supongo que el contexto del filtro impide que [Min Day Value] medida de calcular correctamente, pero he intentado envolverlo en un Calcular sin éxito.

¿Algunas ideas?

@cspress , @Kmow

Entonces, para que conste, finalmente resolví esto.

https://community.powerbi.com/t5/Quick-Measures-Gallery/TRIMMEAN/mp/1074075

¡Me topé con este viejo hilo cuando publiqué esa Medida rápida! 🙂

@ImkeF: ¡es posible que le guste esto, ya que creo que resuelve los problemas con los lazos, que es lo que me volvió loco tratando de resolver!

No he codificado esto, pero una técnica que creo que funcionaría sería implementar un RANGO de sus valores. Luego, podría escribir una fórmula DAX que FILTRARÁ su porcentaje superior e inferior de filas y luego hará su cálculo sobre lo que queda.

¡Gracias! De hecho, su respuesta me hizo darme cuenta de que estaba malinterpretando cómo funciona TRIMMEAN, pensé que estaba excluyendo todos los resultados que se encuentran fuera de un cierto rango, mientras que solo está excluyendo un porcentaje de los resultados. Eso, con suerte, hace las cosas más simples. Probaré con RANK a ver cómo me va. ¡Probablemente publicaré una pregunta de seguimiento!

Dado que esta publicación es uno de los mejores resultados cuando se busca «TRIMMEAN en DAX»… y aún no se ha resuelto, quería señalar esta publicación de blog que lo ayuda a obtener RANKX() para mantener el contexto del filtro. Creo que esto es necesario para que funcione un TRIMMEAN relativo en DAX. Luché con esto por un tiempo, pero esta publicación de blog ayuda mucho.

https://ayadshammout.com/2013/02/19/dax-rankx-function-scenarios/

Una vez que el RANKX esté funcionando (al principio me extrañó que sea una medida, pero si prueba la medida usando una tabla dinámica, puede verificar que la suya está funcionando)…. Luego planeo seguir aproximadamente la metodología en el siguiente enlace No estoy realmente seguro de si hará el trabajo. Voy a tratar de informar de nuevo.

https://social.msdn.microsoft.com/Forums/vstudio/en-US/e193338e-dbbe-462b-99d6-1bd26a033227/how-can-…

@cspress , @Kmow

Entonces, para que conste, finalmente resolví esto.

https://community.powerbi.com/t5/Quick-Measures-Gallery/TRIMMEAN/mp/1074075

¡Me topé con este viejo hilo cuando publiqué esa Medida rápida! 🙂

@ImkeF: ¡es posible que le guste esto, ya que creo que resuelve los problemas con los lazos, que es lo que me volvió loco tratando de resolver!

¡Felicitaciones por eso @Greg_Deckler!

Solo para ponerte celoso lo fácil que hubiera sido en el lado positivo 😉 :

// TRIMMEAN on the bright side :)
let
    Source = Table.Buffer( Table.Sort(Array,{{"Value", Order.Ascending}}) ),
    CountOfTotalRows = Table.RowCount( Source ),
    CutOffAtEachSide = Number.RoundDown((percentage * CountOfTotalRows) / 2),
    RelevantRange = Table.Range( Source, CutOffAtEachSide, CountOfTotalRows - CutOffAtEachSide * 2),
    Result = List.Average( RelevantRange [Value] )
in
    Result

o la versión funcionalizada que acepta los mismos argumentos que la función de Excel (una matriz (lista) y un porcentaje (escalar):

(ListOfValues, Percentage) =>
let
    Source = List.Buffer( List.Sort(ListOfValues, Order.Ascending) ),
    CountOfTotalRows = List.Count( Source ),
    CutOffAtEachSide = Number.RoundDown((Percentage * CountOfTotalRows) / 2),
    RelevantRange = List.Range( Source, CutOffAtEachSide, CountOfTotalRows - CutOffAtEachSide * 2),
    Result = List.Average( RelevantRange)
in
    Result

¡Muy amable @ImkeF! ¿Quizás deberías hacer una serie de blogs sobre la traducción de Excel a M? 🙂

Sí, podría hacer eso.

He pensado en esto por un tiempo en realidad y ya hice algunas funciones a lo largo del tiempo. 🙂

Entradas relacionadas

Deja un comentario Cancelar respuesta