Este ejercicio nos ofrece la oportunidad de explotar el modelo de datos incorporados directamente desde Power Pivot o desde Power Query para analizar los mismos con tablas dinámicas avanzadas, creando columnas calculadas, medidas y KPI's, mediante el uso de las funciones del lenguaje DAX.

Para la realización de esta práctica deberás descargar y descomprimir el siguiente archivo:

Tareas a realizar

Activar complemento PowerPivot

  • Explorar pestaña

Explorar administrador de PowerPivot

  • Pestañas (Inicio, Diseñar, Opciones avanzadas)
  • Vistas (Datos, Diagrama, Área de cálculo)
  • Barra de fórmulas
  • Selector de campos
  • Asistente para funciones

Importar datos al modelo de datos (modo 1)

(desde Power Query)
(con ruta dinámica)

  • Crear un libro vacío Almacen1.xlsx y guardar en la carpeta de trabajo donde están los otros datos.
  • Crear una tabla Ruta con la función CELDA("nombrearchivo")
  • Nombrar la hoja como Ruta (no es imprescindible, pero ayudará a identificarla)
  • Añadir la tabla al Power Query
  • En el editor de consultas:
    • Rastrear desagrupando datos para obtener el dato del directorio.
    • Crear una consulta con los datos del libro Productos.xlsx
    • Crear solo conexion y agregar al modelo de datos
    • Crear una consulta con los datos del archivo de texto Movimientos.txt
    • Crear solo conexion y agregar al modelo de datos
    • Editar la consulta Ruta. 
    • El editor gráfico no deja cambiar el nombre del paso Origen, así que hay que abrir el editor avanzado.
    • Reemplazar Origen por RutaOrigen
    • Copiar las dos lineas entre "let" y "in"
    • Editar consulta Productos con editor avanzado
    • Pegar las dos líneas copiadas después de "let" y antes del paso "Origen"
    • Añadir una coma al final
    • Reemplazar la ruta absoluta dentro de FileContents por (Ruta & "Productos.xlsx") 
    • Repetir para la consulta Movimientos, pero en este caso cambiando la ruta por (Ruta & "Movimientos.txt")
  • Comprobación:
    • Guardar y Cerrar Almacen1.xlsx
    • MOVER los archivos Productos.xlsx, Movimientos.txt y Almacen1.xlsx a otra ubicación
    • Abrir Almacen1.xlsx en la nueva ubicación y comprobar que la actualización no da error de Origen de datos.
    • Volver a MOVER a la carpeta de trabajo original

Importar datos al modelo de datos (modo 2)

(sin usar Power Query)

  • Crear un libro vacío Almacen2.xlsx y guardar en la carpeta de trabajo donde están los otros datos.
  • Abrir el administrador de Power Pivot
    • importar hoja productos.xlsx (Inicio > Obtener datos externos)
    • comprobar posibilidad de filtrar previa a la obtención de datos.
    • importar movimientos.txt
    • comprobar posibilidad de filtrar previa a la obtención de datos.
  • Comprobar que al mover la carpeta de datos, las conexiones dan error.
  • Resolver desde el administrador de Power Pivot en Inicio > Obtener datos externos > Conexiones existentes

Establecer relaciones entre tablas

  • En el libro Almacen1.xlsx acceder al administrador de Power Pivot
  • Activar la vista de diagrama
  • Relacionar Productos con Movimientos por la columna IdProducto
  • Cerrar el administrador de Power Pivot

Tablas dinámicas avanzadas

  • En Almacen1.xlsx crear una tabla dinámica desde el modelo de datos en una nueva hoja de cálculo
  • Nombrar la hoja como TD1
  • Nombrar la tabla dinámica como ProductoFecha
  • Ver el diseño de los campos de tabla (múltiples tablas)
  • Crear la tabla a partir de:
    • Filas: NombreProducto y Fecha
    • Columnas: E/S
    • Sumar Unidades
  • Observar la jerarquía de fechas.
  • Añadir segmentación de datos por NombreProducto
  • Crear otra tabla dinámica en la misma hoja de cálculo
    • Nombrar la tabla dinamica como FechaProducto
    • Filas: Fecha, NombreProducto
    • Columnas: E/S
    • Suma de Unidades
  • Establecer conexión de filtro para ambas tablas dinámicas. Se puede hacer tanto desde las opciones de tabla dinámica como desde las de la segmentación de datos.
  • En otra hoja de nombre TD2 crear otra tabla dinámica
    • Nombre tabla dinámica RecuentoDistinto
    • Filas: Fecha
    • Valores Movimientos->IdProducto (recuento) y Movimientos->IdProducto (recuento distinto)

Ocultar campos en la vista del cliente

  • En el administrador de Power Pivot
    • ocultar IdProducto de la tabla Productos (vista de datos o vista diagrama)
    • ocultar IdMovimiento de la tabla Movimientos (vista de datos o vista diagrama)
    • Alternar visualización de campos ocultos: Inicio > Ver > Mostrar oculto
  • En Excel comprobar que ya no se muestra el campo en el diseño de tablas dinámicas

Creación de columnas calculadas

  • En el administrador de Power Pivot
    • Observar en la tabla Movimientos como se han calculado automáticamente dos columnas nuevas: Fecha (índice de meses) y Fecha (mes).
    • En la tabla Productos crear columna StockInicial como PrecioUnidad * UnidadesEnExistencia
    • Cambiar tipo datos Moneda. "Intentar" hacer lo mismo con PrecioUnidad.

La función RELATED

  • En la tabla Movimientos crear columna ImporteVenta como Unidades * PrecioUnidad (de la tabla Productos).  Dará error, pues hay que usar la función RELATED
  • Resulta: =[Unidades]*RELATED(Productos[PrecioUnidad])
  • Nombre columna: ImporteVenta, tipo Moneda.
(no tiene sentido porque calcula todos los importes también de las Entradas; ya lo arreglaremos)

Creación de medidas

  • Examinar las medidas implícitas (generadas por la tabla dinámica): 
    Opciones avanzadas > Mostrar las medidas implícitas
  • Mostrar y ocultar Área de cálculo.  Dejarla mostrada
  • Situarse bajo la columna ImporteVenta, y pulsar Inicio > Cálculos > Autosuma.  Automáticamente se crea una medida explícita: Suma de ImporteVenta:=sum([ImporteVenta])
  • También se puede escribir directamente seleccionando cualquier celda del área de cálculo y escribiendo la misma fórmula en la barra de fórmulas.
  • Ver como aparece en el diseño de la tabla dinámica
  • Añadir a la tabla dinámica TD2 de las dos maneras: implícita y explícita. Igual resultado.
  • En Excel, ver PowerPivot > Medidas > Administrar
  • En PowerPivot, observar como se muestran las medidas en la Vista de diagrama
  • en Excel crear la medida TotalStockInicial como suma de Productos[StockInicial]
    (hacerlo fuera de una tabla dinámica si no queremos que el campo se incluya automáticamente en los valores de la misma)

Crear KPI's

  • En una nueva hoja TD3, crear una nueva tabla dinámica que muestre:
    • En filas: NombreProducto
    • En valores: StockInicial 
    • Denominamos a la tabla dinámica: EjemploKPI
  • Podríamos aplicar un formato condicional de iconos sobre la columna, pero podemos crear un KPI.
    • Crear un KPI basado en la medida TotalStockInicial (se puede hacer desde la pestaña PowerPivot o en la ventana del propio administrador del mismo)
    • como destino Valor absoluto: 3000 (podría ser una medida previamente creada)
  • Observar el diseño de la lista de campos: Valor, Objetivo y Estado.
  • Activar los tres campos y comprobar su funcionamiento en la tabla dinámica.

Introducción a las funciones DAX

(El contexto en el modelo de datos)

  • Añadir a TD3 otra vez el campo StockInicial y mostrarlo como porcentaje sobre total de la columna.
  • Muestra el porcentaje que representa el valor de inventario de cada producte sobre el total.
  • Añadir una segmentación de datos por NombreProducto
  • Al filtrar el porcentaje no muestra la realidad sino el % sobre el total de los productos filtrados.

CALCULATE

(para modificar el contexto de filtro en una medida de PowerPivot)

  • En el administrador PowerPivot, tabla Productos, explorar la función CALCULATE y modificar la fórmula de StockInicial como sigue:
    TotalStockInicial:=CALCULATE(sum(Productos[StockInicial]);ALL(Productos))
  • Añadir una medida:
    • %StockSobreTotal:=sum(Productos[StockInicial])/[TotalStockInicial]
  • Quitar el KPI TotalStockInicial (ya no tiene sentido como KPI)
  • Crear el KPI %StockSobreTotal refiriendo Valor absoluto como 0,05
  • En la tabla dinámica de TD3, quitar los valores de TotalStockInicial y añadir los de %StockSobreTotal
  • Las medidas en  Power Pivot son mucho más efectivas y certeras que los campos calculados de las tablas dinámicas corrientes.  Se puede comprobar calculando el IMC medio por población de la hoja contactos.xlsx (falta detallar pasos)

SUMX (y AVERAGEX, etc)
  • En la tabla Movimientos tenemos una columna calculada Importe Venta . Veamos como podemos crear una medida que nos facilite el mismo cálculo sin necesidad de crear una columna calculada.  ¡Será más eficiente!
  • En el área de cálculo de la tabla Movimientos introducir la expresión: 
    Suma de ImporteVenta2:=Movimientos[Unidades]*RELATED(Productos[PrecioUnidad]). 
    Dará error porque le falta el contexto de fila del que sí disponía en la columna calculada.
  • Corregirla de la siguiente manera:
    Suma de ImporteVenta2:=SUMX(Movimientos;Movimientos[Unidades]*RELATED(Productos[PrecioUnidad]))
  • Añadirla al diseño de la tabla dinámica de la hoja TD2 y ahora tendremos el mismo resultado calculado de tres maneras diferentes:
    • Medida implícita creada al arrastrar la columna calculada al área de valores del diseño de la tabla dinámica.
    • Medida explícita creada haciendo referencia a la columna calculada.
    • Medida explicita a paritr de los valores base, sin necesidad de columna calculada previa.
  • Para crear medidas con cálculos que se deban efectuar para cada fila, hay que usar las funciones de agregado habituales, pero acabadas en X: SUMX, AVERAGEX, MAXX, MINX, COUNTX, COUNTAX, etc.
DIVIDE

  • Para evitar errores de división por cero (Power Pivor muestra 'infinito'), usar:
    =DIVIDE(numerador; denominador)

FILTER

Ya vimos que las funciones SUMX, COUNTX, AVERAGEX y similares, evalúan una expresión fila a fila dentro de una tabla, como en un hipotético cálculo:

Importe:=SUMX(VENTAS;[UNIDADES]*[PRECIO])

Observemos que el primer argumento es una tabla.

Pues bien, la función FILTER precisamente nos devuelve una tabla filtrada que podemos pasar como argumento a SUMX.

FUNCIONES DE INTELIGENCIA DE TIEMPO

  • Buena práctica: crear las medidas en una tabla independiente.  Para crear una tabla en blanco, copiar una celda vacía de Excel y pegar en PowerPivot.  Añadir ahí todas las medidas y ocultar la columna en las herramientas cliente.
  • Crear tabla de calendario.  ¡Imprescindible!.  Esto permitirá utilizar calendarios personales con datos como: año académico, año fiscal, nombres de meses en otro idioma, etc.
  • Ejemplos (hacer en la BD Neptuno):
    • Fecha Primer Pedido:=FIRSTDATE(Pedidos[FechaPedido])
    • Fecha Último Pedido:=LASTDATE(Pedidos[FechaPedido])
    • Fin de Año:=ENDOFYEAR(Pedidos[FechaPedido];"08-31")
    • Fin de Mes:=ENDOFMONTH(Pedidos[FechaPedido])
    • Importe Venta:=sumx('Detalles de pedidos';[Cantidad]*[PrecioUnidad])
    • Importe Venta Año Anterior:=CALCULATE([Importe Venta];SAMEPERIODLASTYEAR(Calendario[Date]))
    • Total Ventas Año Anterior:=calculate([Importe Venta];PREVIOUSYEAR(Calendario[Date];"08-31"))
    • Variacion:=[Importe Venta]-[Importe Venta Año Anterior]
    • % incremento:=divide([Variacion];[Importe Venta Año Anterior])
    • Acumulado Año Actual:=CALCULATE([Importe Venta];DATESYTD(Calendario[Date];"08-31"))
    • Acumulado Año Anterior:=calculate([Acumulado Año Actual];SAMEPERIODLASTYEAR(Calendario[Date]))

Última modificación: viernes, 7 de septiembre de 2018, 08:22