Population Pyramid using Power View

This is my first blog, actually this is the first time I Google something and don’t find answers, and  I do it myself :), so I decided to write about it, it is not something hard to do, but because Power View is a new tool yet, maybe nobody needed to use it to create  Population Pyramid, I did.

So this is my topic today “Population Pyramid using Power View”.

Population Pyramid is a visualization that  shows the distribution of various age groups in a population, It typically consists of two back-to-back bar graphs, with the population plotted on the X-axis and age on the Y-axis, one showing the number of males and one showing females in a particular population in five-year age groups. “this what Wikipedia says about it”.

 This is an important visualization for demographic statisticians, unfortunately, BI tools’ developers do not care a lot about this type of users, -mainly those users prefer statistical packages like SPSS and SAS- so if you want to use a BI tool to create Population Pyramid it is very likely that you will end up looking for workarounds.

There are a few blogs talk about creating this report in Excel, but I found nothing about it for Power View, although the steps are almost the same.

First, I used DAX to create two new calculated columns in PowerPivot.

Number of Males =IF([Sex]=1,1,0)   this column will have value of 1 if the gender = 1 (Male) and 0 if it is Female

And Number of Females  =IF([Sex]=2,-1,0)  this column will have value of -1 if the gender = 2 (Female) and 0 if it is Male

And then I created new report in Power View with type Stacked bar –see the first Picture-, actually I tried to use Clustered Bar but the result wasn't as expected-see the second picture- .

In the Values field, I added the two new columns (Number of Males and Number of Females) in the Axis Field added the Age Groups field, as in Population Pyramids we use typically  five-year age groups.

The default theme in PowerView is perfect for this report because usually Males bar is colored with blue and Female bar is colored with red, and this what we have.

There is one issue that I couldn’t handle, the number of Females still in minus! Actually not a lot of people noticed that, and if anyone asked you about it, tell him to pretend that it is just a dash ;)