DAX, Excel, PowerPivot

Pirámides de población superpuestas con PowerPivot

En los artículos que dedicábamos a la creación de una pirámide de población empleando PowerPivot (parte1 y parte2), pudimos comprobar la potencia que este complemento para Excel proporciona a aquellos usuarios encargados de realizar determinados análisis de información sobre una base de datos de contenido demográfico.

Continuando la línea de trabajo abierta en aquellos artículos, en esta ocasión abordaremos el desarrollo de pirámides de población superpuestas, un aspecto del trabajo con pirámides demográficas, que consiste en observar y analizar las diferencias de estructura por sexo y edad existentes entre dos poblaciones, mediante la comparación directa de sus respectivas pirámides.

Antes de proseguir quisiera reiterar mi agradecimiento a Ricard Gènova Maleras, demógrafo especializado en análisis poblacionales sanitarios, perteneciente al Servicio de Informes de Salud y Estudios (Subdirección de Promoción de la Salud y Prevención, DG Atención Primaria, Consejería Sanidad CM), por su inestimable labor de revisión y orientación en todos aquellos conceptos sobre creación de pirámides demográficas, que tan valiosos han resultado en la elaboración del presente y los anteriores artículos que hemos publicado acerca de este mismo tema en el blog.

 

Breve introducción a las pirámides de población superpuestas

Si queremos describir gráficamente este escenario de trabajo, en primer lugar tomaremos las pirámides de las poblaciones a comparar…

PiramidesPoblacionSuperpuestasPowerPivot_01

 

…y a continuación colocaremos una pirámide sobre la otra, de forma que podamos apreciar y analizar las diferencias de población existentes entre ambas, finalizando así la operación.

PiramidesPoblacionSuperpuestasPowerPivot_02

 

Si se trata de dos poblaciones estáticas, la tarea no reviste mayor complejidad que la de rellenar las celdas de la hoja Excel con las cifras de las poblaciones correspondientes, y preparar el gráfico con forma de pirámide que combine los valores. Sin embargo, supongamos que tenemos una población base o de referencia, perteneciente al total de individuos de una región, y queremos establecer comparaciones entre ese total y una clasificación de los subgrupos en que dicha población puede ser dividida, como por ejemplo, las zonas geográficas o de atención sanitaria.

Si el número de zonas es muy elevado, aumentará la cantidad de cálculos y pirámides de subgrupos que necesitaremos elaborar para comparar con la pirámide principal de referencia, incrementándose la complejidad del análisis demográfico a realizar.

Es en este punto del problema donde entra en liza PowerPivot, ya que si lo utilizamos para gestionar los datos de las poblaciones con las que debemos trabajar, simplificaremos en gran medida la elaboración de las pirámides de población, así como su posterior fase de análisis.

Aprovechando la pirámide desarrollada en los artículos mencionados anteriormente, y  plasmada en el archivo PiramidePoblacion.xlsx, utilizaremos dicho escenario de trabajo como punto de partida para elaborar, en primer lugar, las pirámides de población de las distintas zonas sanitarias; pasando a continuación a crear la pirámide de referencia, que representa a la población de la Comunidad de Madrid.

 

Las pirámides de zonificación

A simple vista, esta tarea puede parecer la más complicada, debido a que el objetivo de la misma consiste en crear una pirámide por cada una de las zonas sanitarias de la población que estamos manejando.

Nada más lejos de la realidad sin embargo, puesto que si hemos seguido los artículos ya comentados sobre creación de pirámides, nos habremos dado cuenta de que ese trabajo ya lo tenemos hecho en el archivo PiramidePoblacion.xlsx, creado como ejercicio de ejemplo de dichos artículos, gracias al empleo de una segmentación basada en la tabla Zona del modelo de datos de PowerPivot.

PiramidesPoblacionSuperpuestasPowerPivot_03

 

No obstante, con el fin de reorganizar los nombres que utilizaremos en este artículo, vamos a eliminar las medidas y el gráfico de la pirámide que inicialmente habíamos creado para la tabla dinámica. También cambiaremos, en la ventana de PowertPivot, el nombre de la tabla Poblacion por PoblacionZonas, haciendo clic derecho en la pestaña correspondiente a la tabla, y eligiendo la opción de menú «Cambiar nombre».

PiramidesPoblacionSuperpuestasPowerPivot_04

 

A continuación crearemos de nuevo las medidas de la tabla PoblacionZonas, con los siguientes nombres y expresiones en lenguaje DAX.


PoblacionZonasSuma =
SUM ( [Sexo_Codigo] )

PoblacionZonasTotal =
CALCULATE ( COUNTROWS ( PoblacionZonas )ALL ( Edad )ALL ( Sexo ) )

PoblacionZonasPorcentaje =
[PoblacionZonasSuma] / [PoblacionZonasTotal]

 

PiramidesPoblacionSuperpuestasPowerPivot_05

La forma de crear y configurar el gráfico de pirámide para estas poblaciones será la misma que en los artículos ya indicados, con la excepción del borde de las barras, a las que en esta ocasión asignaremos el color blanco, con un ancho de 2 puntos entre las mismas. Recordemos que para dar formato a las barras haremos clic derecho en cualquiera de ellas, seleccionando la opción de menú «Dar formato a serie de datos».

PiramidesPoblacionSuperpuestasPowerPivot_06

 

En la siguiente figura podemos observar el gráfico con las modificaciones que acabamos de comentar (incluyendo la segmentación por el campo Zona_DS de la tabla Zona), mostrando       la pirámide de una de las zonas disponibles.

PiramidesPoblacionSuperpuestasPowerPivot_07

 

La pirámide de referencia

El siguiente paso consistirá en añadir al gráfico actual la pirámide que representará al total de la población, para lo cual nos situaremos en la ventana de PowerPivot, donde repetiremos el proceso de agregación de la tabla Poblacion desde la base de datos origen hasta el modelo de datos de PowerPivot; pero en esta ocasión daremos el nombre PoblacionReferencia a la tabla en PowerPivot, creando también la columna calculada Sexo_Codigo con la expresión DAX:  «=IF([Sexo_ID] = «M», 1, -1)», ya que igualmente necesitaremos sumar la población en función del sexo de los individuos.

PiramidesPoblacionSuperpuestasPowerPivot_08

 

Tras esta operación crearemos las oportunas relaciones entre ambas tablas de población y el resto, quedando el resultado mostrado por la siguiente figura.

PiramidesPoblacionSuperpuestasPowerPivot_09

 

A continuación volveremos a la ventana de Excel, donde haremos clic en el botón «Actualizar» de la ventana «Lista de campos de PowerPivot», para que se refresque su contenido, y se incorpore la tabla PoblacionReferencia que acabamos de agregar a la ventana de PowerPivot.

PiramidesPoblacionSuperpuestasPowerPivot_10

 

En cuanto a las medidas necesarias para generar la pirámide correspondiente a esta población de referencia, en el siguiente bloque de código se muestran las expresiones DAX a utilizar para su creación.


PoblacionReferenciaSuma =
CALCULATE ( SUM ( [Sexo_Codigo] )ALL ( Area ) )

PoblacionReferenciaTotal =
CALCULATE (
    COUNTROWS ( PoblacionReferencia ),
    ALL ( Edad ),
    ALL ( Sexo ),
    ALL ( Area )
)

PoblacionReferenciaPorcentaje =
[PoblacionReferenciaSuma] / [PoblacionReferenciaTotal]

 

Comparadas las medidas de la tabla PoblacionZonas y PoblacionReferencia, encontramos en estas últimas una importante diferencia dentro de las operaciones encargadas de sumar la población y calcular su total, consistente en el uso de la función ALL aplicada a la tabla Zona, la cual es pasada como parámetro. En la medida PoblacionReferenciaSuma utilizaremos, además, la función CALCULATE, para que la operación de suma sobre la tabla de población de referencia se efectúe correctamente.

La función ALL, cuando interviene en una expresión DAX utilizada para construir una medida calculada que empleamos, por ejemplo, en una tabla dinámica, anula aquellos filtros aplicados en la tabla dinámica, que estén relacionados con la tabla pasada como parámetro a la función ALL, y que en nuestro caso, como ya hemos mencionado, afecta a la tabla Zona.

Para comprobar este comportamiento, que al mismo tiempo dará como resultado la pirámide de referencia, en la ventana de campos de PowerPivot arrastraremos el campo PoblacionReferenciaPorcentaje hasta el panel Valores. Esto producirá la inserción, en la tabla dinámica, de nuevas columnas para dicha medida; y en el gráfico de pirámide, de barras para representar las nuevas cifras de población.

PiramidesPoblacionSuperpuestasPowerPivot_11

 

Como siguiente paso aplicaremos el formato de porcentaje a las nuevas columnas de cifras de la tabla dinámica, y a continuación haremos clic derecho en una de las nuevas barras de población del gráfico, seleccionando la opción «Dar formato a serie de datos»; acción esta que abrirá la ventana del mismo nombre, donde modificaremos las siguientes propiedades/valores.

–Relleno: Sin Relleno.

–Color del borde: Línea sólida, color negro.

–Estilos de borde: Ancho de 1 punto.

 

PiramidesPoblacionSuperpuestasPowerPivot_12

 

Repitiendo esta misma operación sobre la otra barra de datos del gráfico completaremos la configuración visual de la nueva pirámide de población de referencia, que quedará superpuesta a la pirámide por zonas, logrando así nuestro objetivo. Este resultado nos permitirá apreciar las diferencias entre los dos tipos de población, facilitando su análisis.

PiramidesPoblacionSuperpuestasPowerPivot_13

 

Llegados a este punto damos  por concluido el presente artículo sobre construcción de pirámides de población superpuestas, empleando PowerPivot como herramienta para modelar nuestros datos. En un próximo artículo abordaremos el desarrollo de pirámides acumuladas, una variedad de pirámide poblacional en la que las barras de la pirámide aportan mayor información, gracias a que permiten la inclusión de una categoría adicional de clasificación.

2 Comentarios

  1. kiquenet

    Grandioso Luismi !!! tendrás una buena máquina virtual para todo esto 🙂

  2. lmblanco

    Hola Enrique

    Gracias por tu interés en el artículo. Lo cierto es que cada vez que tengo que hacer pruebas con PowerPivot y el origen de datos tiene un volumen considerable, tengo que ajustar al máximo la configuración de la máquina para que pueda hacer la obtención de datos. Lo más recomendable es trabajar con máquinas de 64 bits para poder aprovechar toda la memoria instalada 😉

    Un saludo,

    Luismi

Deja un comentario

Tema creado por Anders Norén