Segmentación de datos y KPIs de Power Pivot

Siguiendo con la línea del post anterior, hoy veremos dos herramientas de visualización del programa Excel. Éstas son la segmentación de datos y el uso de KPIs a partir del componente Power Pivot. Ambas se podrían considerar como herramientas de business intelligence puesto que tienen la utilidad de mostrarnos información almacenada en un conjunto de datos de manera visual e interactiva.

En esta entrada explicaremos qué son, para qué nos pueden servir y cómo las podemos utilizar. Como en la entrada de la semana pasada nos apoyaremos en ejemplos prácticos para ilustrar nuestra explicación. Para ello utilizaremos un conjunto de datos de ejemplo almacenado en el fichero “chicago_narcotics” (Haga clic en el enlace para bajarse el fichero de ejemplo).

Segmentación de datos

Consiste en una herramienta que contiene un conjunto de botones que permiten filtrar los datos de una tabla dinámica rápidamente sin la necesidad de abrir listas desplegables para buscar los elementos que se deseen filtrar.

La segmentación de datos es un recurso adicional a las tablas dinámicas que al mismo tiempo es sencillo de utilizar y muy potente en términos de interactividad con el conjunto de datos que disponemos.

Ejemplo 1:

A partir de nuestro conjunto de datos de ejemplo, supongamos que deseamos visualizar en una tabla dinámica dónde y cuándo se ha producido los delitos. Asimismo también deseamos poder discriminar esta información por uno o varios tipos de delitos. Para ello propongo los siguientes pasos:

1. Para saber cómo se distribuyen en el tiempo necesitamos saber el mes en que se ha producido cada delito. Para ello añadiremos una columna nueva con la fórmula mes.
2. Ahora agruparemos los casos por ubicación del delito. Para ello insertaremos una tabla dinámica siguiendo estos pasos:
   • Nos situamos en cualquier celda de la tabla y en la pestaña insertar hacemos clic en la opción “insertar tabla dinámica” y le damos a Aceptar.
   • Arrastramos el campo “Location Description” al área filas, el campo que hemos definido para la formula mes al área columnas y el campo “Case #” a valores (que se estará contando)
3. Por último vamos a la pestaña “analizar” de “herramientas de tabla dinámica” y hacemos clic en “insertar segmentación de datos”. Se nos abrirá una ventana dónde debemos escoger el campo o campos dónde deseamos que se aplique la segmentación. En nuestro caso escogeremos “Description”.

Et volia, ya disponemos de un conjunto de botones con el que seleccionando uno de los delitos (o varios si mantenemos pulsado la tecla ctrl) podemos ver la información deseada para ese delito en concreto.

KPI de Power Pivot

Los KPI (indicadores clave de rendimiento) son medidas visuales de rendimiento. Disponiendo de un indicador objetivo (al cual queremos llegar o queremos evitar) lo comparamos a un valor resultante de un campo calculado. Si este valor cumple los criterios previamente definidos en base al indicador objetivo entonces se indicará como válido de una forma visual (por ejemplo la luz  verde de un semáforo). Por el contrario, si el valor no los cumple se indicará como no válido (mediante una luz roja de un semáforo).

Ejemplo 2:

A partir de nuestro conjunto de datos de ejemplo, supongamos que deseamos ver de una forma visual qué sala de los juzgados (Ward) ha llevado más o menos delitos en función de la media total anual. Sabiendo esta información podremos planificar mejor la derivación de los delitos para cada sala. Para ello propongo los siguientes pasos:

1. Seleccionamos todo el rango de nuestro conjunto de datos y hacemos clic en “agregar a modelo de datos” de la pestaña Power Pivot. Se nos abrirá una nueva ventana de Power Pivot con nuestros datos. Cabe decir que, pese a abrirse una ventana nueva, esta tabla está vinculada a nuestro fichero Excel.
2. Ahora crearemos nuestro campo calculado siguiendo estos pasos:
   • En la pestaña inicio hacemos clic en «Área de cálculo»
   • En la área de celdas que se muestra justo debajo de nuestra tabla hacemos clic en una celda y escribiremos la siguiente función: Suma de casos:=COUNTA([Case #])/distinctcount([Ward])
3. Ahora crearemos nuestro KPI en función de la media de “Case” por “Ward” que es igual a 769. Seguiremos los siguientes pasos:
   • En la pestaña inicio de la ventana de Power Pivot hacemos clic en “Tabla dinámica”.
   • En la pestaña Power Pivot de nuestro Excel hacemos clic en KPI => Nuevo KPI.
   • Indicaremos que el campo base de KPI sea “Suma de casos” y en «definir valor de destino» seleccionaremos «Valor absoluto» y teclearemos 769.
   • Por último indicaremos cómo deseamos que sea nuestra representación visual. En este caso escogeremos la segunda opción, es decir, aquella que se muestra en rojo aquellos valores que se alejan de la media.
4. Para acabar debemos ir a nuestra tabla dinámica y definirla de la siguiente forma:
   1. En filas colocamos el campo Ward
   2. En valores colocamos los campos Valor, Objetivo y Estado de nuestro KPI “Suma de casos” que nos aparece con el icono de un semáforo.

Después de todos estos pasos ya podemos ver con un indicador llamativo qué sala de los juzgados difiere substancialmente de la media global.