DAX, Excel, PowerPivot

Pirámides de población con PowerPivot. Creación del gráfico (y 2)

En la primera parte de este artículo desarrollábamos un modelo de datos en PowerPivot que representaba las cifras de una población por edad y sexo. En esta segunda entrega plasmaremos dichas cifras en un gráfico con forma de pirámide de población.

 

Gráfico de pirámide. Primer acercamiento

En su estado actual, la tabla dinámica ya tendría la información suficiente (cifras de población, edad y sexo) como para intentar crear un gráfico que represente una pirámide de población; aunque adelantamos al lector que en esta primera aproximación no vamos a conseguir el efecto deseado.

Posicionados en la tabla dinámica, dentro de la cinta de opciones de Excel seleccionaremos la opción «Gráfico dinámico», perteneciente al grupo «Herramientas» de la ficha «Opciones», que a su vez está contenida en la ficha de nivel superior «Herramientas de tabla dinámica».

Esta selección abrirá la ventana «Insertar gráfico», que contiene el conjunto de tipos de gráfico disponibles. Aquí nos percataremos de que no existe una plantilla específica para crear un gráfico de pirámide; por lo tanto, entre toda la oferta a nuestra disposición elegiremos, dentro de la categoría «Barra», el tipo «Barra agrupada», que como veremos más adelante será el que mejor se adaptará al resultado que queremos conseguir.

PiramidesPoblacionPowerPivot_22

 

Aceptando esta ventana, el gráfico será creado a partir de los datos de la tabla dinámica, y tal y como habíamos anticipado, el resultado no se parecerá a la imagen presentada al principio del artículo.

PiramidesPoblacionPowerPivot_23

 

No obstante, la principal diferencia radica en el dibujo de la barra correspondiente a la población de hombres, cuyo sentido debería ser hacia la izquierda; el resto de aspectos son básicamente cuestiones de configuración visual, que posteriormente explicaremos cómo resolver.

 

Solucionando la trayectoria de las barras de población

Centrándonos en la barra de población masculina, la solución para conseguir que se dibuje en dirección opuesta a la que actualmente tiene, consiste en poner en negativo los valores de las celdas de la tabla dinámica correspondientes a este segmento de la población.

Si nos encontráramos en una hoja de cálculo simple, sin conexión a PowerPivot, la solución sería tan sencilla como editar las celdas de la columna Hombre, pasando sus valores a negativo, pero estamos en un escenario de trabajo en el que los datos están siendo obtenidos desde el modelo de datos de PowerPivot, por lo que no es posible editar directamente los valores de la tabla dinámica.

Para solucionar esta clase de problemas tendremos que recurrir a la creación de columnas y/o medidas calculadas, que a través de expresiones DAX proporcionen los resultados que necesitamos. En el caso del paso a negativo de los valores de la columna Hombre, abriremos la ventana de PowerPivot, y situándonos en la primera columna vacía disponible en la tabla Población, escribiremos la siguiente expresión en la barra de fórmulas:


=
IF ( [Sexo_ID] = "M"1-1 )

Acabamos de crear una columna calculada que se evaluará para cada fila de la tabla Población, comprobando si el valor del campo Sexo_ID es igual a la letra «M», en caso afirmativo, el valor de la columna en dicha fila será 1, en caso contrario (cuando el campo contenga «H») el valor devuelto será -1.

Seguidamente haremos doble clic sobre su cabecera para asignarle el nombre Sexo_Codigo. También podemos darle nombre haciendo clic derecho sobre la cabecera y eligiendo la opción «Cambiar nombre de columna».

PiramidesPoblacionPowerPivot_24

 

Volviendo nuevamente a la ventana de Excel, eliminaremos el gráfico que habíamos creado en la hoja y desmarcaremos la medida RecuentoPoblacion, quedando vacía la zona de valores de la tabla dinámica.

El próximo paso a dar consistirá en crear una nueva medida con el nombre SumaPoblacion, basada en la siguiente expresión DAX:


=
SUM ( [Sexo_Codigo] )

Al aplicar esta medida a la tabla dinámica, la función SUM realiza la suma de los valores de la columna pasada como parámetro, por lo que las cifras de la población de hombres ya aparecerán en negativo. Esto significa que al volver a crear el gráfico en el modo antes explicado, las barras indicadoras de los valores de población por sexo se dibujarán ahora en direcciones opuestas. Como detalle adicional, en la ficha «Herramientas de tabla dinámica», dentro de la ficha «Diseño», en el grupo «Diseño» desplegaremos la opción «Totales generales», seleccionando el valor «Activado sólo para columnas», que ocultará la columna de totales de fila, ya que su presencia en este contexto resulta irrelevante.

PiramidesPoblacionPowerPivot_25

 

Configuración visual de las barras de población

Aunque las barras del gráfico ya se muestran según el efecto que nos habíamos propuesto como objetivo, sería deseable que su aspecto visual recibiera algunos retoques para mejorar la calidad de su presentación.

En primer lugar haremos clic derecho sobre las etiquetas de los rangos de edad, seleccionando la opción «Dar formato a eje». En la ventana del mismo nombre, dentro del apartado «Opciones del eje», asignaremos el valor «Bajo» a la propiedad «Etiquetas del eje», lo que tendrá como resultado que esta columna de etiquetas quede alineada a la izquierda del gráfico.

PiramidesPoblacionPowerPivot_26

 

A continuación haremos clic derecho sobre cualquiera de las barras del gráfico, eligiendo la opción «Dar formato a serie de datos». En la ventana de configuración de la serie, dentro del apartado «Opciones de la serie», en la propiedad «Superposición de series», desplazaremos el marcador de posición hasta el extremo derecho (totalmente superpuesta), mientras que en la propiedad «Ancho del intervalo» desplazaremos el marcador de posición hasta el extremo izquierdo (sin intervalo en absoluto). De esta forma conseguiremos que las barras aumenten su grosor y eliminen el espacio intermedio entre las mismas, quedando completamente unidas para formar la pirámide de población.

PiramidesPoblacionPowerPivot_27

 

Calculando la población en porcentajes

La representación de los datos obtenida hasta este momento, tanto en la tabla dinámica como en el gráfico de pirámide, se basa en cifras absolutas de población. Sin embargo, lo habitual y recomendable es que dicha representación se realice como proporción de cada grupo de edad y sexo sobre el total de la población.

Por ejemplo, en nuestra tabla dinámica, la población de mujeres con edades comprendidas entre los 55 y 59 años es de 184.888 personas; para obtener el porcentaje que este grupo de población constituye en relación al total de individuos con el que estamos trabajando (6.458.684), dividiremos el grupo entre el total, y formatearemos el resultado como porcentaje, obteniendo 2,86 %.

Si queremos que la tabla dinámica realice esta operación para todos los grupos de población tendremos que añadir cálculos adicionales en forma de medidas, pero antes de eso eliminaremos el actual gráfico de población, ya que volveremos a construirlo a partir de una de las nuevas medidas.

Partimos de la existencia de una medida, SumaPoblacion, que como ya sabemos, devuelve la cifra de población sumando el campo Sexo_Codigo. El siguiente paso consistirá en crear una nueva medida, que al incluirse en la tabla dinámica, proporcione el total de población en todas las celdas.

Nuestra primera reacción podría ser volver a utilizar la medida RecuentoPoblacion, creada en las fases iniciales de nuestro ejemplo, pero pronto nos daremos cuenta de que no sirve para este propósito, ya que aunque esta medida cuenta las filas de la tabla Población, los resultados de las celdas se ven afectados por los campos utilizados en filas y columnas, así como otros filtros que pudiera tener activos la tabla dinámica.

Para que una medida cuente siempre todas las filas de una tabla, sin importar los filtros que pueda haber activos, utilizaremos la función CALCULATE, a la que pasaremos como primer parámetro la operación a realizar, en este caso el recuento de filas de la tabla Población mediante la función COUNTROWS. A continuación pasaremos tantos parámetros como filtros queramos eliminar, utilizando la función ALL(NombreTabla) por cada tabla que de alguna manera esté actuando como filtro.

Bajo tales premisas crearemos una nueva medida llamada TotalGlobalPoblacion, con la siguiente expresión DAX:


=
CALCULATE ( COUNTROWS ( Poblacion )ALL ( Edad )ALL ( Sexo ) )

Al aplicar sobre la tabla dinámica esta medida, todas sus celdas mostrarán el mismo valor: el  total de la población.

PiramidesPoblacionPowerPivot_28

 

Ahora necesitamos una tercera medida que haga la división entre las dos anteriores y muestre el resultado en formato porcentaje. Esta nueva medida tendrá el «original» nombre de PorcentajePoblacion y utilizara la siguiente fórmula:


=[SumaPoblacion] / [TotalGlobalPoblacion]

Como ya vimos en la primera entrega del artículo, para aplicar el formato a esta medida haremos clic derecho en una de sus celdas eligiendo la opción «Formato de número». En la ventana de formato seleccionaremos esta vez la categoría «Personalizada», a través de la cual introduciremos en el campo «Tipo» la siguiente cadena de formato propia.

0,00%;0,00%

 

PiramidesPoblacionPowerPivot_29

 

Esta cadena, además de formatear el número como porcentaje, mostrará sin el signo negativo los valores de la columna de población masculina, aunque internamente, dichos valores seguirán siendo negativos.

PiramidesPoblacionPowerPivot_30

 

A continuación desactivaremos todas las medidas de la tabla dinámica a excepción de PorcentajePoblación, que será la única que permanecerá visible. Seguidamente volveremos a añadir un gráfico de barras agrupadas, en el que además de utilizar los pasos de configuración explicados con anterioridad, agregaremos nuevas características de formato para mejorar su presentación.

 

Aplicar formato de porcentaje al eje horizontal

En primer lugar haremos clic derecho en el eje horizontal de etiquetas seleccionando la opción «Dar formato a eje», que abrirá la ventana de formato, en cuyo apartado «Número» seleccionaremos la categoría de formato «Personalizado» y en el campo «Código de formato» escribiremos la siguiente cadena de formato:

0%;0%

Haciendo clic en el botón «Agregar», la cadena se añadirá a la lista de cadenas personalizadas, de modo que al aceptar la ventana se aplicará el formato al eje horizontal de etiquetas.

PiramidesPoblacionPowerPivot_31

 

Remarcando los bordes de las barras de población

Seguidamente haremos clic sobre alguna de las barras del gráfico, volviendo a elegir la opción «Dar formato a serie de datos». En esta ocasión, dentro del apartado «Color del borde» haremos clic en la opción «Línea sólida», seleccionando el color negro; mientras que en el apartado «Estilos del borde» asignaremos a la propiedad «Ancho» el valor «2 pto». Esta operación la realizaremos para ambos grupos de barras del gráfico.

PiramidesPoblacionPowerPivot_32

 

Después haremos clic derecho sobre las etiquetas de los rangos de edad, seleccionando la opción «Dar formato a eje». En esta ventana de formato asignaremos los mismos valores para las propiedades de color y estilo de borde que acabamos de utilizar para las barras del gráfico.

Como resultado de estas acciones, el gráfico mostrará sus bordes con un contorno claramente remarcado.

PiramidesPoblacionPowerPivot_33

 

Reubicando la posición de la leyenda

En el momento de crear el gráfico, Excel sitúa por defecto la leyenda (campo Sexo_DS) en el lado derecho. No obstante, es posible cambiar la ubicación de este elemento si queremos proporcionar más espacio al dibujo de las barras de población. Para ello, haremos clic derecho en la leyenda y seleccionaremos la opción «Formato de leyenda», en la ventana de formato, dentro del apartado «Opciones de leyenda», haremos clic en la opción «Superior».

PiramidesPoblacionPowerPivot_34

 

Como podemos apreciar, el gráfico ha ganado en superficie de dibujo, pero los indicadores de la leyenda han quedado colocados en orden inverso a las barras. Para solucionar este problema haremos clic en el botón de campos de la leyenda (Sexo_DS), de forma que despleguemos sus opciones de filtro, donde seleccionaremos «Ordenar de Z a A».

PiramidesPoblacionPowerPivot_35

 

Con esta acción, los indicadores de leyenda quedarán colocados adecuadamente, pero ahora nos encontraremos con el inconveniente de que los colores de las barras han quedado invertidos, y hemos perdido el borde de las barras del gráfico.

Restauraremos los bordes de las barras en la manera explicada anteriormente, mientras que en cuanto a los colores, para cada lado de la pirámide haremos clic derecho en una de las barras, y en la opción «Relleno de forma», cambiaremos el color actual por el que originalmente tenía el gráfico.

PiramidesPoblacionPowerPivot_36

 

Para completar los ajustes que estamos realizando sobre la leyenda, arrastraremos ésta hasta que quede situada al nivel del elemento superior de la pirámide, y aumentaremos su anchura, para que los indicadores queden más separados.

PiramidesPoblacionPowerPivot_37

 

Despejar el área de campos y agregar título

Seguidamente haremos clic derecho en cualquiera de los botones de campos del gráfico y seleccionaremos la opción «Ocultar todos los botones de campos en gráfico». Con este movimiento impediremos que el usuario aplique filtros sobre el eje horizontal y/o vertical de la pirámide, manteniendo su estructura consistente y evitando así la posibilidad, por ejemplo, de ocultar rangos de edad o alguno de los sexos. No obstante, seguirá existiendo tal posibilidad de filtro desde la tabla dinámica.

PiramidesPoblacionPowerPivot_38

 

Por otra parte, en el grupo de fichas «Herramientas del gráfico dinámico» haremos clic en la ficha «Presentación», y dentro del grupo «Etiquetas» haremos clic en la opción «Título del gráfico», que desplegará una serie de elementos entre los que elegiremos «Encima del gráfico», añadiéndose un cuadro de texto al gráfico, que editaremos para asignarle un título. Llegados a este punto habremos completado el desarrollo de nuestra pirámide de población.

PiramidesPoblacionPowerPivot_39

 

Dinamizando los datos de la pirámide mediante segmentaciones

Aunque hemos logrado el objetivo propuesto de crear una pirámide de población, resultaría interesante dotarla de cierto valor añadido, tal y como vamos a hacer en este apartado.

Si observamos el modelo de datos en la ventana de PowerPivot, caeremos en la cuenta de que todavía no hemos hecho uso de la información que sobre zonificación sanitaria existe en el mismo, por lo que podemos aprovechar estos datos para construir un filtro que muestre la pirámide en base a la población de una o varias de estas zonas sanitarias.

Las tablas dinámicas de PowerPivot, además del filtro tradicional, incorporan un nuevo tipo de filtro denominado «segmentación», que además de la funcionalidad habitual de filtrado ofrece una interfaz de usuario más flexible para la manipulación de los valores a tratar.

Vamos a crear en nuestra pirámide una segmentación basada en la información de zona. Para ello, en el panel de la lista de campos, arrastraremos el campo Zona_DS de la tabla Zona hasta el bloque «Segmentaciones de datos horizontales», obteniendo como resultado una segmentación situada encima de la tabla dinámica y el gráfico de pirámide.

PiramidesPoblacionPowerPivot_40

 

Para filtrar los datos mediante la segmentación simplemente tenemos que seleccionar el nombre de la zona sanitaria que queramos emplear como filtro. También es posible filtrar simultáneamente varias zonas manteniendo pulsada la tecla Ctrl, mientras vamos haciendo clic en las zonas a filtrar (como muestra la siguiente figura). Si queremos eliminar todos los filtros activos haremos clic en el icono situado a tal efecto en la parte superior derecha de la segmentación.

PiramidesPoblacionPowerPivot_41

 

Y llegados a este punto damos por finalizado el artículo, en el que a lo largo de sus dos entregas hemos mostrado el modo de construir una pirámide de población en Excel 2010, utilizando PowerPivot como herramienta de gestión de los datos poblacionales. No obstante, la potencia de esta tecnología va más allá del mero tratamiento de la información demográfica, abarcando su campo de acción a cualquier entorno en el que tengamos que realizar un análisis con elevados volúmenes de datos.

 

3 Comentarios

  1. lmblanco

    Hola preguntoncojonero

    He visto el comentario que has puesto en este artículo ya que ha llegado a mi cuenta de correo. Dicho comentario debería aparecer publicado al final del texto del artículo, aunque debe haber algún problema a este respecto que estoy revisando en la configuración del blog, a ver si conseguimos solucionarlo y que aparezcan correctamente los comentarios enviados por los lectores.

    Respecto a la pregunta que formulabas en tu comentario:

    «no hay código de descaga ?? salu2grz»

    Quería comentarte que no hay código fuente para descargar ya que, básicamente, el código es el correspondiente a las fórmulas DAX de PowerPivot (que se pueden copiar/pegar directamente del artículo), y el resto consiste en operaciones de configuración del gráfico que forma la pirámide de población.

    Disculpa el inconveniente y el retraso en la respuesta, y muchas gracias por tu interés en el artículo 😎

    Luismi

  2. anonymous

    En los artículos que dedicábamos a la creación de una pirámide de población

  3. anonymous

    In the articles that were dedicated to the creation of a population pyramid using PowerPivot ( part1

Deja un comentario

Tema creado por Anders Norén