In this post, we are going to try something more advanced. If you feel lost, try starting with Part 1. Hopefully this will inspire you and give you a flavor of what types of infographics you can create with Excel. Our subject is going to be high schools in Nashville. We are going to create an infographic that answers the question: What if Nashville was a class of 25 students? We are going to color each student based on their standardized test score. Here is what the final result will look like:
We are going to start with a dataset from the Nashville Chamber of Commerce’s Education Report Card. I have already prepared my data inside PowerPivot for this example. If you are using your own dataset, you can get to where I am by creating a measure for each segment of the population.
In my state, a minimum ACT test score of 19 is the entrance requirement for state colleges and universities. An ACT score equal to or greater than 21 is the minimum necessary to qualify for a state scholarship. These benchmarks put the students into these three groups:
- Not Eligible for State College
- Eligible for State College
- Eligible for State College and Scholarship
I created measures for these groups that show the number of students in my 25 person class in each category. I am rounding up because I don’t want my infographic to show half a student being eligible for college. Here are the measures I created:
If we put these measures on a bar chart, we can see a single bar that is divided into three groups. The total is 25.
Our goal is to have 5 bars with a value of 5 that contains the whole population of students. We want to fill up a cut out image with this layout:
To get 5 bars that evenly distribute the whole dataset, we need the first bar to show the first 5 values and then the second bar to show values 6 through 10 and then the third bar to show 11 through 15 and so on.
To achieve this, we are going to use a disconnected slicer technique (example). I am going to add a table to PowerPivot that has the values of 0 through 4. I am going to call this field “Distribution.”
Next, I’m going to add a measure that is the minimum value in that column. This will allow me to use the Distribution value in measures because it will return one value.
Now we are going to incorporate this measure into a formula in order to move the bar down in increments of 5. Here are the DAX formulas I used:. (This does seem a little complicated but this is the easiest way I could think to do this. If you can think of a better way, I would love to hear it in the comments.)
Here is what our chart looks like with the new formulas and the bar distribution field on the Y-axis:
I’m going to set the X-axis scale so the Max is 5 in the Min is 0. Now our chart looks perfect. I applied the techniques in a previous post (Part 1) to make the bars wide.
Now all we need to do is take the “25 student” cut out images that we created in PowerPoint and overlay them on the chart. We will resize the chart so that it fits perfectly behind the images. See a previous posts (Part 2) for details on how to create the images.
I did a few things to make this look like a report such as adding a title, legend, definitions, and a detailed table. Looking at the final result, I can easily see which schools have a majority eligible for state college and schools that do not.
I can also see a few schools that improved between 2010 and 2013. At Hillsboro high school, they got 2 more students in our 25 person class to eligible!
You can download example workbooks here:
I hope this has been informative. Please comment below if you have any questions and let us know if you thought this was helpful.