Patrón del total acumulado en Power BI

Una medida común que encontraremos útil con frecuencia es aquella que computa el total acumulado de una métrica a lo largo de las categorías de alguna dimensión, normalmente el tiempo. El acumulado (ya sea como suma, promedio o cualquier otro tipo de agregación), es uno de los cálculos más utilizados a la hora de analizar los datos de un negocio, debido a que nos proporciona la capacidad de ver y comprender claramente los aspectos relacionados con las tendencias que se producen en los datos. En este artículo analizaremos el problema desde algunos de los escenarios más comunes que nos encontraremos al desarrollar este tipo de cálculo.

Escenario 1: Total anual hasta la fecha

Es probable que una de las primeras medidas que nos interese evaluar sea las ventas acumuladas a lo largo del tiempo, teniendo en cuenta cada año del contexto de filtro, de forma que el cálculo se resetee al inicio de cada uno de ellos. A tal efecto podemos usar en un primer lugar una de las funciones de inteligencia de tiempo incorporadas en DAX llamada TOTALYTD:

VentasYTD =
TOTALYTD ( SUM ( Ventas[Ventas] ); Calendario[Fecha] )

Esta expresión es una simplificación de la fórmula más general usando la función CALCULATE:

VentasYTD =
CALCULATE ( SUM ( Ventas[Ventas] ); DATESYTD ( Calendario[Fecha] ) )

Esta segunda fórmula tiene la ventaja de que nos permite modificar programáticamente aún más el contexto de evaluación añadiendo argumentos de filtro adicionales a la función CALCULATE. Por ejemplo, la siguiente fórmula computa las ventas acumuladas desde el inicio de cada año, solo para los productos de la categoría «Audio» con un precio de coste unitario superior a 10:

TotalYTDaumentado =
CALCULATE (
    SUM ( Ventas[Ventas] );
    DATESYTD ( Calendario[Fecha] );
Producto[CosteUnitario] > 10;
CategoriaProducto[Categoria] = «Audio»
)

Estas expresiones funcionan correctamente con cualquier otra columna de la dimensión temporal como las semanas del año o los días del mes, por lo que podremos expandir los niveles de la jerarquía temporal sin ningún problema.

Escenario 2: Total acumulado desde el inicio hasta la fecha

Si lo que buscamos es calcular el acumulado desde el inicio del conjunto de datos, sin que el cálculo se resetee al comienzo de cada año, no podemos recurrir a ninguna función de inteligencia de tiempo integrada. Tenemos que utilizar código DAX genérico.

Este escenario es el más interesante de todos, vamos a ver exactamente lo que necesitamos. Si utilizamos la medida básica de la suma de las ventas, cuya fórmula es:

TotalVentas =
SUM ( Ventas[Ventas] )

Y añadimos en las filas de una matriz los campos Año y Mes, DAX añade los dos filtros correspondientes a la tabla Calendario, y como dicha tabla tiene una relación de 1-a-N con la tabla Ventas, el filtro se propaga y obtenemos la siguiente matriz:

La fórmula anterior computa la suma de las ventas visibles dentro de contexto de filtro actual. Si nos fijamos en la celda resaltada, vemos que las ventas del mes de marzo de 2011 ascienden a 111.939,59. La complejidad de este escenario reside en que, si queremos obtener la suma de las ventas desde el inicio del conjunto de datos hasta el final de marzo de 2011, tenemos que encontrar otro contexto de filtro que en vez de filtrar solamente el periodo de marzo de 2011, filtre todo el periodo comprendido entre el inicio del conjunto de datos y el final de marzo de 2011. Es importante darse cuenta de que el nuevo contexto depende del actual, es más, el nuevo contexto es mayor que el actual.

Por lo tanto, para computar el acumulado necesitamos:

• Encontrar el final del periodo de fechas que deja visible el contexto de filtro actual, en nuestro caso, el final de marzo de 2011.
• Usando el valor anterior, crear un filtro que deje visible en nuestra dimensión temporal todas las fechas hasta el final de marzo de 2011.

Teniendo esto en mente, la fórmula será:

AcumuladoVentas =
CALCULATE (
[TotalVentas];
    FILTER ( ALL ( Calendario ); Calendario[Fecha] <= MAX ( Calendario[Fecha] ) )
)

Usamos la función ALL para que FILTER pueda iterar toda la tabla Calendario, ignorando el contexto de filtro introducido por las filas de la matriz. Por otro lado, comparamos la fecha actual (definida por el contexto de fila introducido por FILTER), con MAX(Calendario[Fecha]). Como esta expresión es parte de los argumentos de filtro de la función CALCULATE, se evalúa bajo el contexto original, y por lo tanto, en nuestro ejemplo, el resultado será la última fecha de marzo de 2011.

Igual que en el escenario 1, esta expresión será válida para cualquier otro campo de la dimensión temporal.

Cabe mencionar también, que la expresión del total acumulado puede ser segmentada por cualquier atributo dimensional para generar comparaciones interesantes. Por ejemplo, en la siguiente imagen tenemos el acumulado de las ventas comparando aquellas que tienen algún tipo de descuento con las que no tienen ninguno:

Como podemos observar, el espacio entre ambas categorías va aumentando con el paso del tiempo, por lo que queda claro que nuestra política de descuentos está teniendo un efecto tangible en las ventas de la empresa.

Existe todavía un problema con nuestra fórmula, y es que, si nuestra dimensión temporal contiene fechas posteriores a la última fecha de nuestra tabla de hechos, que será lo más común, obtendremos el siguiente resultado:

Aunque el resultado de nuestra fórmula es correcto, ya que las ventas acumuladas hasta por ejemplo el 2T de 2015 son, con los datos que tenemos, las ventas que hay de momento en nuestro conjunto de datos, es probable que no queramos mostrar aquellas filas para las cuales no existen ventas. Para ello, podemos usar simplemente la función IF para comprobar si existen ventas en el periodo correspondiente al contexto de filtro actual, y en caso de que no existan, que nos devuelva como resultado BLANK. La fórmula sería:

AcumuladoVentas =
IF (
    COUNTROWS ( Ventas ) > 0;
    CALCULATE (
[TotalVentas];
        FILTER ( ALL ( Calendario ); Calendario[Fecha] <= MAX ( Calendario[Fecha] ) )
    )
)

Escenario 3: total acumulado como columna calculada

En ocasiones, nos encontraremos con la necesidad de trabajar con la información del acumulado de un valor en forma de columna calculada, y no que solo esté disponible a nivel de visualización en un informe.

En el caso de que tengamos en la tabla una columna de fecha sobre la que queramos acumular, el problema es sencillo de resolver con DAX.

Si queremos tener en cuenta el año como filtro, de manera que el cálculo del acumulado se resetee al inicio de cada año como hicimos en el escenario 1 con medidas, la fórmula sería:

AcumuladoYTD =
VAR FechaFila = Tabla[Fecha]
RETURN
    CALCULATE (
        SUM ( Tabla[Valor] );
        FILTER (
Tabla;
Tabla[Fecha] <= FechaFila
&& YEAR ( Tabla[Fecha] ) = YEAR ( FechaFila )
        )
    )

Y el resultado:

La línea de código señalada es la que deberíamos eliminar si lo que queremos es calcular el acumulado sin tener en cuenta el año del contexto de fila, como hicimos en el escenario 2.

Podríamos también tomar en cuenta la columna Calidad, de forma que los valores distintivos actúen como filtro y la columna Valor se vaya acumulando por separado en cada caso. Para ello solo hay que añadir la variable CalidadFila en el primer contexto de fila introducido al definir la columna calculada, y luego incluir el filtro correspondiente en la función FILTER:

Estas expresiones son muy útiles para entender los contextos de fila, ya que tenemos dos de ellos en la misma tabla. El primero se introduce al crear la columna calculada y el segundo al usar la función FILTER. En este caso hemos utilizado variables, que usan el contexto de evaluación donde fueron definidas y no el contexto donde son usadas, pero también podríamos escribir la misma expresión en «DAX antiguo», es decir, sin usar variables. En ese caso tendríamos que hacer uso de una función muy útil llamada EARLIER, que nos permite acceder al valor de una columna, en un contexto de fila anterior al último introducido.

En nuestro caso, dentro de la iteración introducida por la función FILTER, tenemos que acceder al valor de la columna Fecha en la primera iteración, la introducida al crear la columna calculada, es decir, al valor que guardamos en la variable. En consecuencia, podríamos escribir también la última expresión de la siguiente forma:

AcumuladoYTDCalidadEarlier =
CALCULATE (
    SUM ( Tabla[Valor] );
    FILTER (
Tabla;
Tabla[Fecha] <= EARLIER ( Tabla[Fecha] )
&& YEAR ( Tabla[Fecha] ) = YEAR ( EARLIER ( Tabla[Fecha] ) )
&& Tabla[Calidad] = EARLIER ( Tabla[Calidad] )
    )
)

Y obtendríamos el mismo resultado:

Todo el código DAX de este artículo ha sido formateado con «DAX Formatter»