Cómo crear KPIs en Excel usando Power Pivot y DAX
En este video tutorial aprenderás a crear KPI en Excel, usando Power Pivot y el lenguaje Data Analysis Expressions (DAX). Además de tocar el tema de Tablas dinámicas y el Modelo de datos en Excel.
Ver Video Cómo crear KPIs en Excel usando Power Pivot y DAX
Suscríbete al canal de EXCELeINFO en YouTube para aprender más de Excel y macros.
Qué son los KPI
Los indicadores clave de rendimiento (KPI) son medidas visuales de rendimiento. un KPI está diseñado para ayudar a los usuarios a evaluar rápidamente el valor actual y el estado de una métrica con respecto a un objetivo definido. El KPI evalúa el rendimiento del valor, definido por una medida base (también conocido como campo calculado en Power Pivot en Excel , con respecto a un valor del objetivo , también definido por una medida o por un valor absoluto. Si el modelo no tiene medidas, vea crear una medida.
Habilitar Power Pivot en Excel
En caso de que no tengas la pestaña de Power Pivot habilitada, te vas a la pestaña Programador > Complementos COM > Microsoft Office Power Pivot for Excel 2013.
Figura 1. Habilitar Power Pivot en Excel.
Agregar Tablas al modelo de datos
Tenemos dos tablas, una de VENTAS y otra de EMPLEADOS. Como vamos a usar Power Pivot, será necesario añadir ambas tablas al Modelo de datos. Sigue los siguiente pasos:
-
Activamos la hoja Ventas.
-
En la pestaña Power Pivot elegimos Agregar al modelo datos.
-
Marcamos que nuestra Tabla tiene encabezados.
-
Repetimos lo mismo con la hoja Empleados.
Figura 2. Añadimos las tablas al Modelo de datos de Power Pivot.
Dentro de la ventana de Power Pivot presionamos el botón Vista de diagrama y hacemos la relación de los campos ID_EMPLEADO de ambas tablas.
Figura 3. Relaciones entre tablas dentro Power Pivot.
Tabla dinámica
Ahora que ya tenemos nuestras tablas relacionadas, vamos a insertar una Tabla dinámica para ver los empleados, sus llamadas y sus ventas. Dentro de Power Pivot presionamos el botón Tabla dinámica y la insertamos en una hoja nueva.
Movemos el campo MES y NOMBRE_EMPLEADO a la sección Filas. Ahora movemos los campos LLAMADAS y VENTAS a la sección Valores.
Figura 4. Tabla dinámica en Excel usando un Modelo de datos.
Crear el valor base o medida usando DAX
El valor base será la división de Ventas entre las llamadas y con eso obtendremos el valor de la Productividad, para luego comparar ese valor con el objetivo. Sigamos los siguientes pasos:
-
Ve a la pestaña Power Pivot.
-
Medidas > Nueva medida.
-
En Nombre de la medida escribimos Productividad.
-
En fórmula escribimos =DIVIDE(SUM(VENTAS[VENTAS]),SUM(VENTAS[LLAMADAS]))
-
Formato Porcentaje con 2 decimales.
Crear el valor objetivo
El valor objetivo será el valor que se comparará con el valor base para que el KPI muestre los colores. Sigamos los siguientes pasos:
-
Ve a la pestaña Power Pivot.
-
Medidas > Nueva medida.
-
En Nombre de la medida escribimos Objetivo Productividad.
-
En fórmula escribimos =0.0091.
-
Formato Porcentaje con 2 decimales.
Crear KPI
Ahora vamos a crear el KPI que nos ayudará a validar el valor objetivo contra el valor base y así mostrar un semáforo con los colores correspondientes a cada umbral.
-
Nos vamos a la pestaña Power Pivot.
-
KPI > Nuevo KPI.
-
Campo base de KPI (valor) elegimos Productividad.
-
En Medida elegimos Objetivo Productividad.
-
En los umbrales de estado definimos que abajo del 80% se ponga en color rojo, entre 80% y 100% amarillo y más del 100%, verde.
Figura 5. Crear KPI en Excel usando Power Pivot y DAX.
Resultado final
Añadimos las nuevas medidas a nuestra Tabla dinámica. Añadimos la medida de Productividad y la sección Estado del KPI.
Figura 6. Tabla dinámica en Excel mostrando los indicadores KPI.
Descarga el archivo de ejemplo
KPIs en Power Pivot – EXCELeINFO.zip
Si te gustó este tutorial por favor regístrate en nuestra Lista de correo y Suscríbete a nuestro canal de YouTube para que estés siempre enterado de lo nuevo que publicamos.