Completado de una tabla sustituyendo valores inexistentes por ceros

Un caso con el que nos encontramos con cierta frecuencia es aquel en el que tenemos datos tabulares pero no disponemos de valores para todas las combinaciones de filas y columnas, y querríamos tener al menos ceros que nos permitan operar con la tabla completa.

Para ver cómo podemos resolver este problema vamos a partir de la siguiente tabla de datos:

Tabla de datos

Como vemos, se trata de una tabla en la que se muestran las ventas de tres posibles productos a lo largo de los cuatro trimestres del año, tabla en la que muchas de las combinaciones de trimestres y productos no existen.

Si, tras leer esta tabla con Power BI, llevamos los datos a una matriz, el resultado es el siguiente:

Matriz de datos en Power BI

...con blanks en todas aquellas intersecciones de filas y columnas para las que no hay datos en nuestra tabla original.

Pues bien, para crear una tabla a partir de la leída que contenga valores en todas las combinaciones de filas y columnas vamos a comenzar identificando los posibles valores en filas y en columnas -lo que podemos conseguir usando la función VALUES-, y calculando el producto cartesiano de ambos conjuntos con CROSSJOIN:

Tabla = 
    CROSSJOIN(
        VALUES(Data[Trimestre]);
        VALUES(Data[Producto])
    )

El resultado se muestra en la siguiente imagen:

Producto cartesiano de los elementos de filas y columnas

A esta tabla le faltaría la columna de "Ventas" para incluir todos los datos que necesitamos. La añadimos con la función ADDCOLUMNS:

Tabla = 
    ADDCOLUMNS(
        CROSSJOIN(
            VALUES(Data[Trimestre]);
            VALUES(Data[Producto])
        );
        "Ventas"; 0
    )

Ahora ya tenemos la tabla con valores nulos:

Producto cartesiano de los elementos de filas y columnas con ventas

Si llevamos esta tabla a una matriz confirmamos que el aspecto es el deseado:

Matriz con los datos de la nueva tabla

Ahora necesitamos "sumar" los valores de ambas tablas, la original y la que acabamos de crear. Para ello vamos a recurrir a unirlas (verticalmente) con la función UNION:

Tabla = 
    VAR 
        tabla1 = ADDCOLUMNS(
                CROSSJOIN(
                    VALUES(Data[Trimestre]);
                    VALUES(Data[Producto])
                );
                "Ventas"; 0
            )
    VAR
        tabla2 = Data
    RETURN
        UNION(
            tabla1;
            tabla2
        )

El resultado es el siguiente:

Unión de las dos tablas

Como nuestro objetivo es tener una tabla con una única entrada para cada combinación de filas y columnas, la agregamos según estos criterios. El problema con el que nos encontramos aquí es que la función SUMMARIZE, por ejemplo, exige una tabla de base, no una tabla calculada. De forma que vamos a crear una nueva tabla calculada a partir de la anterior:

Tabla completa = 
    SUMMARIZE(
        Tabla;
        Tabla[Trimestre];
        Tabla[Producto];
        "Ventas"; SUM(Tabla[Ventas])
    )

El resultado es el esperado:

Tabla agregada

Ahora ya podemos llevar esta tabla a una matriz y comprobar que incluye todos los valores:

Matriz con los datos agregados
DAX functions involved
Submitted by admin on Wed, 10/30/2019 - 10:06