martes, 26 de febrero de 2019

Power Pivot: Funciones DAX como SUMX o CALCULATE

En la entrada anterior del blog hablamos sobre como crear columnas calculadas y medidas dentro del entorno de Power Pivot, y en este entorno el lenguaje empleado es DAX (Expresión de análisis de datos en inglés).

Me gustaría listar algunas de las funciones DAX más utilizadas, aunque sin duda este es un mundo casi infinito...
Un dato interesante, al igual que en la hoja de cálculo, el máximo de funciones a anidar es de 64 niveles.

Las más frecuentes:
- RELATED
- SUM, AVERAGE, COUNT, DISTINCTCOUNT
- SUMX, AVERAGEX, COUNTX
- CALCULATE
- ALL, FILTER


A priori cualquiera de estas funciones se puede emplear indistintamente tanto en la construcción de 'columnas calculadas' como de 'medidas', si bien algunas tienen más sentido en un contexto que otro...

Trabajaremos sobre el modelo de datos del post anterior, donde teníamos dos tablas añadidas al modelo ('TblVentas' y 'TblPrecios'), relacionadas por un campo 'item-producto'.

Con el fin de obtener de manera detallada el importe en euros de las ventas (como producto de unidades por precio unitario, creamos en su momento una columna calculada que llamamos
Parcial2: =TblVentas[Unidades]*RELATED(tblPrecios[Precio Unitario])

donde vemos que se emplea la función RELATED(columna) la cual devolverá el dato correspondiente de acuerdo a la relación entre tablas existentes.
En el ejemplo
RELATED(tblPrecios[Precio Unitario])
recupera el 'Precio unitario' que corresponde al 'producto' asociado a cada registro o fila...
Podríamos decir que equivale a un típico BUSCARV.


Otras típicas funciones son SUM(columna), AVERAGE(columna), COUNT(columna) o DISTINCTCOUNT(columna) las cuales realizan los cálculos respectivos sobre los valores de la columna.

Por ejemplo, crearemos unas medidas para obtener diferente información (condicionado al diseño de nuestra tabla dinámica):
Suma1:=SUM([Parcial2])
Media1:=AVERAGE([PreciosR])
Conteo1:=COUNT([Cliente])
Distintos1:=DISTINCTCOUNT([Producto])


Power Pivot: Funciones DAX como SUMX o CALCULATE



Si trasladamos las medidas creadas a nuestra tabla dinámica veremos:

Power Pivot: Funciones DAX como SUMX o CALCULATE



Se comprueba rápidamente que los resultados de estas funciones se ajustan al detalle de nuestra tabla dinámica.

De manera similar, pero empleando las funciones SUMX(tabla, expresión), AVERAGEX(tabla, expresión) o COUNTX(tabla, expresión) creamos las siguientes medidas:
Suma2:=SUMX(TblVentas;TblVentas[Unidades]*RELATED(tblPrecios[Precio Unitario]))
Media2:=AVERAGEX(TblVentas;TblVentas[Unidades]*RELATED(tblPrecios[Precio Unitario]))
Conteo2:=COUNTX(TblVentas;TblVentas[Unidades]*RELATED(tblPrecios[Precio Unitario]))


Power Pivot: Funciones DAX como SUMX o CALCULATE



El resultado es equivalente sobre la tabla dinámica:

Power Pivot: Funciones DAX como SUMX o CALCULATE



Otro ejemplo para calcular las 'ventas medias' empleando dos medidas:
MediaVentas2:=AVERAGE([Parcial2])
MediaVentas1:=AVERAGEX(TblVentas;TblVentas[Unidades]*RELATED(tblPrecios[Precio Unitario]))


Power Pivot: Funciones DAX como SUMX o CALCULATE



Y el resultado en la tabla dinámica:

Power Pivot: Funciones DAX como SUMX o CALCULATE



En general, las funciones SUMX, AVERAGEX o COUNTX son funciones que 'iteran'; a diferencia de SUM, AVERAGE o COUNT que pueden operar en bloques de datos y son muy eficientes.
SUMX, AVERAGEX o COUNTX recorren nuestros datos fila por fila y, por tanto, son menos eficiente.
Por esto, conviene usar SUMX, AVERAGEX o COUNTX solo cuando no pueda usar SUM, AVERAGE o COUNT.

En el ejemplo de 'MediaVentasl' anterior, se calcula el promedio de las ventas (como producto de Unidades por Precio) en cada registro en la tabla de ventas, al multiplicar primero [Unidades] por [Precio unitario] en cada fila y luego promediar esas sumas.


Por no eternizar el artículo dejaré para el siguiente post las tres funciones restantes a comentar:
- CALCULATE
- ALL, FILTER

4 comentarios:

  1. Hola. En realidad SUM y SUMX en rendimiento es igual,no hay diferencia de rendimiento. Saludos

    ResponderEliminar
    Respuestas
    1. Gracias por tu comentario...
      Si comparas una y otra trabajando sobre millones de registros si comprobarás la diferencia
      ;-)

      Un cordial saludo

      Eliminar
  2. Hola, como podría hacer en power pivot para calcular porcentajes teniendo en cuenta distintos criterios y que estos porcentajes varíen en función de dichos criterios. Gracias.

    ResponderEliminar
    Respuestas
    1. Hola Esther,
      para aplicar criterios se suelen emplear funciones tipo SUMX o COUNTX... pero siempre dependerá del calculo necesitado
      Slds

      Eliminar

Nota: solo los miembros de este blog pueden publicar comentarios.