Totales parciales con SUMMARIZE. Profundizando en el uso de DAX como lenguaje de consulta (y 9)

La cláusula ROLLUP perteneciente a la función SUMMARIZE genera, en las columnas de agregación que especifiquemos, filas adicionales de resultados acumulados (totales parciales) para las columnas numéricas utilizadas en la consulta.

A modo de ejemplo, en la siguiente consulta utilizamos ROLLUP para la columna StoreType de la tabla DimStore, por lo que obtendremos, además de las cifras de venta por tipo de almacén, una última fila adicional con el total de las ventas.


EVALUATE
SUMMARIZE (
    'DimStore',
    ROLLUP ( 'DimStore'[StoreType] ),
    "Ventas"SUM ( 'FactSales'[SalesAmount] )
)

 

ProfundizandoUsoDAXComoLenguajeConsulta_32

 

Partiendo de este resultado, supongamos ahora que necesitamos que los tipos de almacén aparezcan ordenados, pero manteniendo la fila de total al final. Si usamos la cláusula ORDER BY en la consulta, el resultado no será totalmente satisfactorio, ya que  se ordenarán los nombres, pero la fila de total quedará al principio.


EVALUATE
SUMMARIZE (
    'DimStore',
    ROLLUP ( 'DimStore'[StoreType] ),
    "Ventas"SUM ( 'FactSales'[SalesAmount] )
)
ORDER BY [StoreType]

 

ProfundizandoUsoDAXComoLenguajeConsulta_33

 

Aplicando el modificador DESC a la cláusula ORDER BY solucionamos el problema solamente en parte, ya que conseguimos que el total vuelva a colocarse al final del conjunto de resultados, pero como es lógico, los nombres quedan ordenados en sentido descendente.


EVALUATE
SUMMARIZE (
    'DimStore',
    ROLLUP ( 'DimStore'[StoreType] ),
    "Ventas"SUM ( 'FactSales'[SalesAmount] )
)
ORDER BY [StoreType] DESC

 

ProfundizandoUsoDAXComoLenguajeConsulta_34

 

Controlando la ubicación de los acumulados mediante ISSUBTOTAL

Si queremos ordenar los nombres en sentido ascendente, pero manteniendo la ubicación del total al final, debemos recurrir a ISSUBTOTAL, otra de las cláusulas de SUMMARIZE, la cual crea una columna de tipo lógico, en la que cada valor nos indica si la fila actual del conjunto de resultados corresponde a un acumulado (total parcial) o bien se trata de una fila normal de datos. El truco en este caso reside en aplicar una doble ordenación: primero por la columna de ISSUBTOTAL y a continuación por la de datos.


EVALUATE
SUMMARIZE (
    'DimStore',
    ROLLUP ( 'DimStore'[StoreType] ),
    "Ventas"SUM ( 'FactSales'[SalesAmount] ),
    "Subtotal_StoreType"ISSUBTOTAL ( 'DimStore'[StoreType] )
)
ORDER BY
    [Subtotal_StoreType],
    [StoreType]

 

ProfundizandoUsoDAXComoLenguajeConsulta_35

 

Ahora vamos a añadir la columna ContinentName, de la tabla DimGeography, como nueva columna de agrupación a la consulta. De esta manera, cada fila de acumulado resultante se corresponderá con el total de ventas de un continente para todos los tipos de almacén.


EVALUATE
SUMMARIZE (
    'DimStore',
    ROLLUP ( 'DimStore'[StoreType] ),
    'DimGeography'[ContinentName],
    "Ventas"SUM ( 'FactSales'[SalesAmount] )
)

 

ProfundizandoUsoDAXComoLenguajeConsulta_36

 

La interpretación de los datos que obtenemos, no obstante, es poco amigable, por lo que nuevamente usaremos ISSUBTOTAL y ordenaremos las columnas tal y como muestra el siguiente bloque de código, consiguiendo que las filas de acumulado se sitúen proporcionando la información de un modo más legible.


EVALUATE
SUMMARIZE (
    'DimStore',
    ROLLUP ( 'DimStore'[StoreType] ),
    'DimGeography'[ContinentName],
    "Ventas"SUM ( 'FactSales'[SalesAmount] ),
    "Subtotal_StoreType"ISSUBTOTAL ( 'DimStore'[StoreType] )
)
ORDER BY
    [ContinentName],
    [Subtotal_StoreType],
    [StoreType]

 

ProfundizandoUsoDAXComoLenguajeConsulta_37

 

Ya que ROLLUP admite más de una columna de agregación como parámetro, vamos a añadirle la columna ContinentName para obtener en esta ocasión, además de los acumulados anteriormente mencionados, una nueva fila de total con el importe de las ventas para todos los tipos de almacén y continentes. Al mismo tiempo crearemos con ISSUBTOTAL sendas columnas para StoreType y ContinentName, que junto a un adecuado orden facilitarán la lectura de los datos.


EVALUATE
SUMMARIZE (
    'DimStore',
    ROLLUP ( 'DimStore'[StoreType], 'DimGeography'[ContinentName] ),
    "Ventas"SUM ( 'FactSales'[SalesAmount] ),
    "Subtotal_StoreType"ISSUBTOTAL ( 'DimStore'[StoreType] ),
    "Subtotal_ContinentName"ISSUBTOTAL ( 'DimGeography'[ContinentName] )
)
ORDER BY
    [Subtotal_StoreType],
    [StoreType],
    [Subtotal_ContinentName],
    [ContinentName]

 

ProfundizandoUsoDAXComoLenguajeConsulta_38

 

Agrupar los acumulados con ROLLUPGROUP

A pesar de existir la posibilidad de utilizar varias columnas con ROLLUP, en determinadas situaciones quizá nos interese tener una única fila de acumulado para todas las columnas de agregación incluidas en esta cláusula, lo que podemos lograr  mediante ROLLUPGROUP, otra de las cláusulas de SUMMARIZE.

Esta cláusula se utiliza como parámetro de ROLLUP, colocando las columnas de agregación como parámetros de la llamada a la función ROLLUPGROUP.


EVALUATE
SUMMARIZE (
    'DimStore',
    ROLLUP ( ROLLUPGROUP ( 'DimStore'[StoreType], 'DimGeography'[ContinentName] ) ),
    "Ventas"SUM ( 'FactSales'[SalesAmount] ),
    "Subtotal_StoreType"ISSUBTOTAL ( 'DimStore'[StoreType] )
)
ORDER BY
    [Subtotal_StoreType],
    [StoreType],
    [ContinentName]

 

ProfundizandoUsoDAXComoLenguajeConsulta_39

 

Cabe destacar que la información sobre ROLLUPGROUP disponible en la documentación online de la función SUMMARIZE es una aportación de Marco Russo, puesto que dicha entrada, correspondiente a la referencia de las funciones del lenguaje DAX, no disponía inicialmente de reseña alguna acerca de esta cláusula.

Para lograr un comportamiento similar en Transact-SQL haremos uso del operador ROLLUP cuando especifiquemos las columnas a agrupar, así como de la función GROUPING en la lista de columnas a mostrar de la instrucción SELECT.

SELECT
CASE
	WHEN (GROUPING(StoreType)=1) THEN '--Total General--'
	ELSE StoreType
END AS StoreType,
CASE
	WHEN (GROUPING(ContinentName)=1) THEN '--Acumulado por tipo almacén--'
	ELSE ContinentName
END AS ContinentName,
SUM(SalesAmount) AS Ventas
FROM DimStore
INNER JOIN DimGeography
ON DimStore.GeographyKey = DimGeography.GeographyKey
INNER JOIN FactSales
ON DimStore.StoreKey = FactSales.StoreKey
GROUP BY StoreType, ContinentName WITH ROLLUP
ORDER BY StoreType, GROUPING(ContinentName), ContinentName

 

Y llegados a este punto damos por concluida esta serie de artículos en los que hemos expuesto diversas técnicas para la creación de consultas contra modelos tabulares empleando el lenguaje DAX, espero que os resulten de utilidad.

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *