Power Pivot y modelo de datos en Excel: relaciones y medidas DAX

Hasta ahora, cuando necesitabas combinar dos tablas, traías columnas con BUSCARV o BUSCARX (lección 6). Funciona, pero tiene límites: duplica datos, hace pesado el archivo y una hoja no admite más de 1.048.576 filas. Power Pivot y el modelo de datos resuelven esos tres problemas: cargas cada tabla una sola vez, las relacionas como en una base de datos y calculas con un lenguaje creado para eso, DAX. Es el mismo motor que usa Power BI, así que lo que aprendas aquí te servirá en el curso de Power BI.

El caso: tres tablas de una tienda de café

Una tienda tiene sus datos en tres tablas de Excel (con formato de tabla, lección 4):

Datos de ejemplo para seguir la lección:

Productos: P1 Café molido (costo 12), P2 Taza (costo 9), P3 Prensa francesa (costo 45). Clientes: C1 y C3 son de Lima; C2, de Arequipa.

Paso 1: cargar las tablas al modelo

1. Haz clic en la tabla Ventas y ve a Power Pivot › Agregar al modelo de datos. Repite con Productos y Clientes.
2. Si los datos vienen de archivos externos, mejor aún: impórtalos con Power Query (lección 11) y elige Cerrar y cargar en… › Solo crear conexión con la casilla Agregar estos datos al modelo de datos. Así no ocupan ninguna hoja y pueden superar el millón de filas.

Paso 2: relacionar las tablas (modelo en estrella)

En la ventana de Power Pivot, pulsa Vista de diagrama y arrastra Ventas[IdProducto] sobre Productos[IdProducto], y Ventas[IdCliente] sobre Clientes[IdCliente]. También puedes hacerlo desde Datos › Relaciones.

El resultado es un modelo en estrella: la tabla de hechos en el centro y las dimensiones alrededor, cada relación de "uno" (la dimensión, donde cada Id aparece una vez) a "varios" (Ventas, donde se repite). Ya no necesitas copiar el nombre del producto ni la ciudad en la tabla de ventas: el modelo los encuentra a través de las relaciones.

Paso 3: medidas con DAX

Una medida es un cálculo que se evalúa según los filtros de la tabla dinámica: el mismo "Ventas totales" da el total general, el de Lima o el de marzo, según dónde lo pongas. Créalas en Power Pivot › Medidas › Nueva medida o con clic derecho sobre la tabla en la lista de campos de la tabla dinámica › Agregar medida.

Ventas Totales := SUMX(Ventas, Ventas[Cantidad] * Ventas[Precio])

Costo Total := SUMX(Ventas, Ventas[Cantidad] * RELATED(Productos[Costo]))

Margen := [Ventas Totales] - [Costo Total]

% Margen := DIVIDE([Margen], [Ventas Totales])

Ventas Lima := CALCULATE([Ventas Totales], Clientes[Ciudad] = "Lima")

Qué hace cada función:

• SUMX recorre la tabla fila por fila, calcula la expresión y suma los resultados. Aquí multiplica cantidad por precio en cada venta.
• RELATED trae un valor de la tabla del lado "uno" siguiendo la relación: el costo del producto de cada venta. Es el reemplazo de BUSCARV dentro del modelo.
• DIVIDE divide y devuelve vacío en lugar de error si el denominador es 0.
• CALCULATE evalúa una medida cambiando los filtros: "las ventas totales, pero solo de Lima".

Con los datos de ejemplo: Ventas Totales = 775, Costo Total = 444, Margen = 331, % Margen = 42,7% y Ventas Lima = 605.

¿Medida o columna calculada?

Regla práctica: si lo vas a poner en filas, columnas o segmentaciones, columna calculada; si lo vas a poner en Valores, medida.

Paso 4: analizar con tablas dinámicas

Inserta una tabla dinámica y elige Usar el modelo de datos de este libro. En la lista de campos verás las tres tablas. Arrastra Productos[Categoría] a filas, Clientes[Ciudad] a columnas y las medidas a valores. Las segmentaciones y escalas de tiempo funcionan igual que en la lección 9, pero ahora pueden filtrar por campos de cualquier tabla.

Reportes con formato libre: las funciones CUBO

Si necesitas un informe con un diseño fijo que la tabla dinámica no permite, usa las funciones de cubo, que leen el modelo celda por celda:

=VALOR.CUBO("ThisWorkbookDataModel"; "[Measures].[Ventas Totales]")
=VALOR.CUBO("ThisWorkbookDataModel"; "[Measures].[Margen]"; "[Clientes].[Ciudad].&[Lima]")

El atajo: crea la tabla dinámica y usa Analizar tabla dinámica › Herramientas OLAP › Convertir en fórmulas. Cada celda se transforma en una fórmula VALOR.CUBO que puedes mover a donde quieras.

Ventas Arequipa := CALCULATE([Ventas Totales], Clientes[Ciudad] = "Arequipa")
→ 170   (90 de tazas + 80 de café)

N.º de ventas := COUNTROWS(Ventas)                  → 6
Ticket promedio := DIVIDE([Ventas Totales], [N.º de ventas])
→ 129,17

Tabla dinámica (Ventas Totales):
                    Arequipa   Lima    Total
Café molido            80      360      440
Prensa francesa                245      245
Taza                   90                90
Total                 170      605      775

Resumen

• El modelo de datos guarda varias tablas relacionadas, sin duplicar datos y sin el límite de filas de una hoja.
• Organízalo en estrella: hechos en el centro, dimensiones alrededor, relaciones de uno a varios.
• Las medidas DAX se calculan según los filtros: SUMX, RELATED, DIVIDE y CALCULATE cubren la mayoría de los casos.
• Columnas calculadas para clasificar; medidas para los valores.
• Las funciones CUBO convierten el modelo en informes con diseño libre.

En la próxima lección pondrás a prueba todo el curso con los ejercicios que más se piden en las pruebas de Excel de los procesos de selección, resueltos paso a paso.