Campos Calculados, Elementos y Técnicas Avanzadas de Tablas Dinámicas

Al finalizar esta lección serás capaz de:

• Crear campos y elementos calculados para métricas personalizadas en tablas dinámicas
• Implementar tablas dinámicas con modelo de datos para relaciones entre tablas
• Usar funciones DAX básicas en Power Pivot para cálculos avanzados
• Aplicar técnicas de ordenamiento, agrupación y diseño profesional de TDs

Los campos calculados te permiten crear nuevas columnas de cálculo dentro de la tabla dinámica sin modificar los datos originales.

Crear un Campo Calculado

1. Hacé clic en la tabla dinámica
2. Análisis de tabla dinámica > Campos, elementos y conjuntos > Campo calculado
3. Poné un nombre descriptivo
4. Escribí la fórmula usando los nombres de los campos existentes
5. Aceptar

Ejemplos de Campos Calculados Útiles

Margen Bruto:

Nombre: MargenBruto
Fórmula: = Total - Costo

Porcentaje de Margen:

Nombre: PctMargen
Fórmula: = (Total - Costo) / Total

Comisión del Vendedor (5% sobre ventas):

Nombre: Comision
Fórmula: = Total * 0.05

Precio Promedio Efectivo:

Nombre: PrecioPromedioEfectivo
Fórmula: = Total / Cantidad

IVA:

Nombre: IVA
Fórmula: = Total * 0.21

Advertencia Sobre Campos Calculados

Los campos calculados operan sobre los AGREGADOS, no sobre las filas individuales. Esto significa:

PctMargen = SUMA(Total - Costo) / SUMA(Total)

...que es DIFERENTE a:

Promedio de (Total - Costo) / Total por cada fila

Para la mayoría de los cálculos financieros (margen total, comisión total), esto está bien. Pero para promedios ponderados, puede dar resultados diferentes al esperado.

Solución: Si necesitás cálculos a nivel de fila, agregá la columna calculada a los datos fuente (en la tabla estructurada) en lugar de crear un campo calculado en la TD.

A diferencia de los campos calculados (columnas nuevas), los elementos calculados crean filas o agrupaciones nuevas dentro de un campo existente.

Ejemplo: Agrupar Regiones en Zonas

Tenés regiones: Buenos Aires, Litoral, Cuyo, NOA, NEA, Patagonia.

Querés crear:

• Zona Centro: Buenos Aires + Litoral
• Zona Oeste: Cuyo
• Zona Norte: NOA + NEA
• Zona Sur: Patagonia

1. Clic en un elemento del campo "Región" en la tabla dinámica
2. Análisis > Campos, elementos y conjuntos > Elemento calculado
3. Nombre: "Zona Centro"
4. Fórmula: = 'Buenos Aires' + Litoral
5. Repetí para cada zona

Ahora la tabla dinámica muestra tanto las regiones individuales como las zonas agrupadas.

Limitación Importante

No podés usar elementos calculados y agrupación de datos en el mismo campo. Si agrupaste las regiones, no podés crear elementos calculados sobre ellas.

¿Qué es el Modelo de Datos?

El Modelo de Datos permite crear tablas dinámicas que RELACIONAN múltiples tablas, como una base de datos relacional. En lugar de hacer BUSCARV para cruzar datos, establecés relaciones entre tablas.

Crear una Tabla Dinámica con Modelo de Datos

1. Asegurate de tener tablas estructuradas (Ctrl + T) con nombres descriptivos
2. Al insertar tabla dinámica, marcá "Agregar estos datos al modelo de datos"
3. Power Pivot > Administrar > Vista de diagrama
4. Arrastrá para crear relaciones entre tablas (ej: Ventas.ID_Producto → Productos.ID)

Ahora podés arrastrar campos de CUALQUIER tabla relacionada a tu tabla dinámica.

Relaciones entre Tablas

Ejemplo de modelo:

Ventas (tabla de hechos)
  ├── ID_Producto → Productos (dimensión)
  ├── ID_Cliente → Clientes (dimensión)
  ├── ID_Vendedor → Empleados (dimensión)
  └── Fecha → Calendario (dimensión)

Una vez creadas las relaciones:

• Podés arrastrar "Categoría" de la tabla Productos a filas
• Y "Total" de la tabla Ventas a valores
• Sin ninguna fórmula de cruce

Medidas DAX Básicas

Power Pivot permite crear medidas con el lenguaje DAX (Data Analysis Expressions):

Total Ventas := CALCULATE(SUM(Ventas[Total]))

Margen := DIVIDE(
    SUM(Ventas[Total]) - SUM(Ventas[Costo]),
    SUM(Ventas[Total]),
    0)

Ventas Año Anterior := CALCULATE(
    SUM(Ventas[Total]),
    DATEADD(Calendario[Fecha], -1, YEAR))

Crecimiento := DIVIDE(
    [Total Ventas] - [Ventas Año Anterior],
    [Ventas Año Anterior],
    0)

Estas medidas son más poderosas que los campos calculados porque:

• Se recalculan dinámicamente según el contexto de filtro
• Pueden hacer cálculos temporales (año anterior, YTD, promedio móvil)
• Funcionan correctamente con múltiples niveles de agregación

Diseño de la Tabla Dinámica

En la pestaña Diseño:

Diseño Tabular es el mejor para exportar datos o crear reportes impresos porque:

• Cada campo tiene su propia columna
• Las etiquetas se repiten (no quedan celdas vacías)
• Se parece a una tabla normal

Gráficos Dinámicos

Creá gráficos vinculados a tablas dinámicas:

1. Clic en la tabla dinámica
2. Insertar > Gráfico dinámico
3. Elegí el tipo (barras, líneas, circular, etc.)
4. El gráfico se actualiza automáticamente al filtrar la TD

Los gráficos dinámicos heredan las segmentaciones y escalas de tiempo de la tabla dinámica.

Formato Condicional en Tablas Dinámicas

Podés aplicar formato condicional que se adapta cuando la TD cambia:

1. Seleccioná los valores de la TD
2. Inicio > Formato condicional
3. Aplicá escalas de color, barras o semáforos
4. El formato se mantiene incluso al reorganizar la TD

1. Campo calculado da resultado incorrecto para promedios: Recordá que opera sobre agregados, no sobre filas. Si necesitás un promedio real por fila, calculalo en los datos fuente.

2. Modelo de datos no muestra todas las tablas: Asegurate de que todas las tablas están en el modelo (Power Pivot > Administrar).

3. Relación incorrecta: Siempre relacioná desde la tabla de hechos (muchos) a la dimensión (uno). Ventas(muchos) → Productos(uno).

4. Gráfico dinámico pierde formato: Si recreas la TD, el gráfico se desconecta. Mejor actualizar la TD existente que recrearla.

• Los campos calculados crean métricas nuevas sin modificar datos originales
• Operan sobre agregados, lo cual es correcto para sumas pero puede ser engañoso para promedios
• El Modelo de Datos permite relacionar tablas sin BUSCARV
• Power Pivot y DAX llevan las tablas dinámicas al siguiente nivel
• El diseño Tabular es mejor para exportación y reportes
• Los gráficos dinámicos heredan los filtros de la tabla dinámica

1. ¿Sobre qué operan los campos calculados de una tabla dinámica?

   • a) Sobre cada fila individual
   • b) Sobre los agregados/resúmenes ✓
   • c) Sobre los datos fuente
   • d) Depende de la función

2. ¿Qué permite el Modelo de Datos que no se puede hacer con TDs normales?

   • a) Filtrar datos
   • b) Relacionar múltiples tablas ✓
   • c) Crear gráficos
   • d) Sumar valores

3. ¿Qué diseño de TD es mejor para exportar datos?

   • a) Compacto
   • b) Esquema
   • c) Tabular ✓
   • d) Ninguno

Desafío: Sistema de Análisis Multi-Tabla

1. Creá tres tablas: Ventas (1000 filas), Productos (50 filas), Clientes (100 filas)
2. Agregá las tres al Modelo de Datos
3. Establecé relaciones: Ventas→Productos por código, Ventas→Clientes por ID
4. Creá una tabla dinámica con campos de las tres tablas
5. Agregá campos calculados: Margen, Comisión, IVA
6. Creá un gráfico dinámico vinculado
7. Aplicá formato condicional a los valores

Recurso en Video

📹 Power Pivot y Modelo de Datos en Excel — Dostin Hurtado

Introducción práctica al Modelo de Datos y Power Pivot con ejemplos empresariales en español.