Promedio de cálculo lento con cuartiles

[MatheusLPS]

Hola, ¿puede alguien ayudarme con una tarea? Tengo datos de tabla para este ejemplo con solo dos columnas: Key anda data.

Quiero calcular el valor medio por clave.

Mis datos tienen algunos valores de esquema que quiero eliminar del cálculo promedio.

Tarea: Eliminar delineadores:

1-Calcular primer cuartil

2-Calcular tercer cuartil

3-Calcular los límites exteriores.

4-Calcular el valor medio sin contornos.

Estoy usando una tabla visual para agregar el valor clave y una medida para calcular todo esto. Pero es lento.

AverageCalc = 

var k1 = 0.25
var k2 = 0.75

//This part is to calculate the boundaries of quartiles. Took the code from here:
https://community.powerbi.com/t5/Desktop/Percentile-exc-Error/td-p/637337

var NumValues = COUNT ( Planilha1[Data] )
var LowerBound = 1 / ( NumValues + 1 )
var UpperBound = NumValues / ( NumValues + 1 )

//If my k is inside the boundaries, calculate quartile 1 and quartile 3
var q1 = 
    IF (
        AND ( k1 >= LowerBound, k1 <= UpperBound ),
        PERCENTILE.EXC ( Planilha1[Data], k1)
    )

var q3 = 
    IF (
        AND ( k2 >= LowerBound, k2 <= UpperBound ),
        PERCENTILE.EXC ( Planilha1[Data], k2)
    )

//Calculate interquartile
var iqr = q3 - q1

//calculate inner boudaries from my data
var lowValue = q1 - (1.5 * iqr)
var highValue = q3 + (1.5 * iqr)

//calculate outer boudaries from my data
var ultraLowValue = q1 - (3.0 * iqr)
var ultraHighValue = q3 + (3.0 * iqr)

//calculate my average without outliers
var media = 
    CALCULATE(
        AVERAGE(Planilha1[Data]),
        Planilha1[Data] <= ultraHighValue
    )

return
media

Mis datos: http://www.sharecsv.com/s/5e5b0aad68beec3c8764bcf737ef2bf5/Test_Data.csv

[lbendlin]

Ese es un código interesante. A los condicionales les falta el valor alternativo, por lo que terminas haciendo muchas matemáticas en BLANK(). Algunas de sus variables no se utilizan en el resultado final, pero creo que DAX es lo suficientemente inteligente como para no calcularlas.

En cualquier caso, cargue la consulta en DAX Studio e investigue el plan de consulta y los tiempos de servidor allí. Usé sus datos de muestra y vi esto

Y esto

(esto continúa para 3000 líneas más)

Definitivamente tiene espacio para mejorar. La primera pregunta sería - ¿realmente tiene que calcular eso en todas las claves?

Modifiqué el código ligeramente, pero el verdadero problema es el cálculo promedio al final. No estoy seguro de cómo acelerar eso.

AverageCalc = 
var k=SELECTEDVALUE(Planilha1[key])
var k1 = 0.25
var k2 = 0.75
var NumValues = COUNT ( Planilha1[Data] )
var LowerBound = 1 / ( NumValues + 1 )
var UpperBound = NumValues / ( NumValues + 1 )
var q1 = IF ( k1 >= LowerBound && k1 <= UpperBound, PERCENTILE.EXC ( Planilha1[Data], k1),0)
var q3 = IF ( k2 >= LowerBound && k2 <= UpperBound, PERCENTILE.EXC ( Planilha1[Data], k2),0)
var ultraHighValue = 4*q3 - 3*q1
var media = CALCULATE(
        AVERAGE(Planilha1[Data]),
        Planilha1[key]=k,
        Planilha1[Data] <= ultraHighValue
    )
return media

Aquí hay una versión que es posible que desee considerar. Todavía tiene un plan de consulta horrible, pero parece ejecutarse un poco más rápido.

AverageCalc = 
var k1 = 0.25
var k2 = 0.75
var NumValues = COUNT ( Planilha1[Data] )
var LowerBound = 1 / ( NumValues + 1 )
var UpperBound = NumValues / ( NumValues + 1 )
var q1 = IF ( k1 >= LowerBound && k1 <= UpperBound, PERCENTILE.EXC ( Planilha1[Data], k1),0)
var q3 = IF ( k2 >= LowerBound && k2 <= UpperBound, PERCENTILE.EXC ( Planilha1[Data], k2),0)
var ultraHighValue = 4*q3 - 3*q1
var media = AVERAGEX(filter(Planilha1,Planilha1[Data] <= ultraHighValue),Planilha1[Data])
return media