Columnas de agrupación en SUMMARIZE con tablas no relacionadas. Profundizando en el uso de DAX como lenguaje de consulta (8)

Si las tablas de las columnas empleadas en la consulta con SUMMARIZE no están relacionadas se producirá un error, como vemos en el siguiente bloque de código, donde combinamos las columnas StoreType y BrandName de las tablas DimStore y DimProduct, para obtener las ventas realizadas.


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

 

ProfundizandoUsoDAXComoLenguajeConsulta_27

 

Tal y como se explica en el mensaje de error, la columna BrandName no existe en la tabla de entrada DimStore, utilizada como primer parámetro de la función SUMMARIZE; aunque en este caso sería más correcto decir que la tabla DimProduct, que contiene la columna BrandName, no está relacionada con la tabla de entrada usada en la consulta.

Si el primer parámetro de SUMMARIZE representa una tabla con las columnas a visualizar (directa o indirectamente a través de relaciones), en el caso de que no existan relaciones con las tablas necesarias podemos crear una tabla al vuelo con las columnas que tenemos que mostrar empleando la función GENERATE.


EVALUATE
SUMMARIZE (
    GENERATE (
        VALUES ( 'DimStore'[StoreType] ),
        VALUES ( 'DimProduct'[BrandName] )
    ),
    [StoreType],
    [BrandName],
    "Ventas tipo almacén marca producto"SUM ( 'FactSales'[SalesAmount] )
)
ORDER BY
    [StoreType],
    [BrandName]

 

ProfundizandoUsoDAXComoLenguajeConsulta_28

 

En el caso de que el número de columnas sin relacionar que tengamos que presentar sea superior a dos, podemos optar por la función CROSSJOIN para crear la tabla de entrada utilizada por SUMMARIZE, como vemos en la siguiente consulta, donde creamos una medida calculada para realizar la suma de las ventas, a la que aplicaremos un formato monetario, y que emplearemos en una condición de filtro para evitar mostrar importes vacíos.


DEFINE
    MEASURE 'FactSales'[Ventas] =
        SUM ( 'FactSales'[SalesAmount] )
EVALUATE
FILTER (
    SUMMARIZE (
        CROSSJOIN (
            VALUES ( 'DimStore'[StoreType] ),
            VALUES ( 'DimProduct'[BrandName] ),
            VALUES ( 'DimDate'[CalendarYear] )
        ),
        [StoreType],
        [BrandName],
        [CalendarYear],
        "Ventas tipo almacén-marca producto-año venta"FORMAT ( 'FactSales'[Ventas], "Currency" )
    ),
    NOT ( ISBLANK ( [Ventas] ) )
)
ORDER BY
    [StoreType],
    [BrandName],
    [CalendarYear]

 

ProfundizandoUsoDAXComoLenguajeConsulta_29

 

Partiendo del anterior resultado, supongamos ahora que necesitamos calcular el porcentaje que la combinación de ventas por tipo de almacén, marca de producto y año de venta ha supuesto con respecto al total de las ventas realizadas.

Para obtener estos resultados precisamos añadir al código de la consulta dos nuevas medidas calculadas: la primera, a la que llamaremos VentasTotales, realizará mediante la función SUMX la suma de la columna SalesAmount para todas las filas de la tabla FactSales; empleando la función ALL para ignorar cualquier filtro que pudiera encontrarse activo.

La segunda medida, PorcentajeVentas, efectuará la operación encargada de calcular el porcentaje de las ventas realizadas. Dicho valor será convenientemente formateado cuando lo mostremos junto al resto de columnas de la consulta.


DEFINE
    MEASURE 'FactSales'[Ventas] =
        SUM ( 'FactSales'[SalesAmount] )
    MEASURE 'FactSales'[VentasTotales] =
        SUMX ( ALL ( 'FactSales' ), 'FactSales'[SalesAmount] )
    MEASURE 'FactSales'[PorcentajeVentas] = ( 'FactSales'[Ventas] / 'FactSales'[VentasTotales] )
EVALUATE
FILTER (
    SUMMARIZE (
        CROSSJOIN (
            VALUES ( 'DimStore'[StoreType] ),
            VALUES ( 'DimProduct'[BrandName] ),
            VALUES ( 'DimDate'[CalendarYear] )
        ),
        [StoreType],
        [BrandName],
        [CalendarYear],
        "Ventas tipo almacén-marca producto-año venta"FORMAT ( 'FactSales'[Ventas], "Currency" ),
        "Ventas totales", 'FactSales'[VentasTotales],
        "Porcentaje ventas"FORMAT ( 'FactSales'[PorcentajeVentas], "Percent" )
    ),
    NOT ( ISBLANK ( [Ventas] ) )
)
ORDER BY
    [StoreType],
    [BrandName],
    [CalendarYear]

 

ProfundizandoUsoDAXComoLenguajeConsulta_30

 

Aunque la medida calculada VentasTotales se visualiza junto al resto de las columnas, podría perfectamente permanecer oculta, ya que su finalidad consiste en actuar como operando para hallar el porcentaje de las ventas.

Para obtener este mismo resultado mediante SQL, tendremos que establecer, al igual que en anteriores ejemplos, las combinaciones necesarias entre las tablas implicadas en la consulta.

SELECT StoreType, BrandName, CalendarYear,
SUM(SalesAmount) AS [Ventas tipo almacén-marca producto-año venta],
(SELECT SUM(SalesAmount) FROM FactSales) AS [Ventas totales],
FORMAT( ( SUM(SalesAmount) / (SELECT SUM(SalesAmount) FROM FactSales) ) , 'P', 'ES-ES' )  AS [Porcentaje ventas]
FROM DimStore
INNER JOIN FactSales
ON DimStore.StoreKey = FactSales.StoreKey
INNER JOIN DimProduct
ON FactSales.ProductKey = DimProduct.ProductKey
INNER JOIN DimDate
ON FactSales.DateKey = DimDate.Datekey
GROUP BY StoreType, BrandName, CalendarYear
ORDER BY StoreType, BrandName, CalendarYear

Empleando la tabla de resultados numéricos como tabla de entrada

En los últimos ejemplos de SUMMARIZE hemos planteado la manera de abordar la creación de una consulta introduciendo un problema consistente en la inexistencia de relaciones entre la tabla de entrada y el resto de tablas de las columnas de agregación implicadas en dicha consulta, lo cual nos llevaba a recurrir a soluciones un tanto artificiosas, tales como el empleo de las funciones GENERATE o CROSSJOIN en el código a escribir.

Observando las características de estas consultas nos percataremos de que la tabla FactSales, empleada para calcular la suma de ventas, se encuentra relacionada con las tablas utilizadas para las columnas de agregación. Si a esto unimos la capacidad de utilizar FactSales como tabla de entrada en SUMMARIZE, podremos construir una consulta más simple que evite el uso de trucos rebuscados. Como ventaja añadida, esta consulta no devuelve filas con la columna de ventas vacía, lo que también nos libera de aplicar el correspondiente filtro.


EVALUATE
SUMMARIZE (
    'FactSales',
    'DimStore'[StoreType],
    'DimProduct'[BrandName],
    'DimDate'[CalendarYear],
    "Ventas"FORMAT ( SUM ( 'FactSales'[SalesAmount] )"Currency" )
)
ORDER BY
    [StoreType],
    [BrandName],
    [CalendarYear]

 

ProfundizandoUsoDAXComoLenguajeConsulta_31

Deja un comentario

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