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:

Infographic Post Part 3 Final

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.

Infographic Post Part 3 Model

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:

Not Eligible for State College:=25-[Eligible for State College]-[Eligible for State College and Scholarship]
Eligible for State College:=ROUND(25*[% 19 to 20],0)
Eligible for State College and Scholarship:=ROUND(25*[% 21 and Above],0)

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.

Infographic Post Part 3 Begining Bar

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:

Infographic Post Part 3 Target

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.

Infographic Post Part 3 Bar Design

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.”

Infographic Post Part 3 Distribution Table

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.

Infographic Post Part 3 Min Distribution Formula

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.)

Eligible for State College and Scholarship Bar:=IF([Eligible for State College and Scholarship]-[Min Distribution]*5<0,BLANK(),[Eligible for State College and Scholarship]-[Min Distribution]*5)
Eligible for State College Bar:=IF([Eligible for State College]+[Eligible for State College and Scholarship]-[Min Distribution]*5<0,BLANK(),IF([Eligible for State College]+[Eligible for State College and Scholarship]<5,[Eligible for State College],[Eligible for State College]+[Eligible for State College and Scholarship]-[Min Distribution]*5))
Not Eligible for State College Bar:=IF([Not Eligible for State College]+[Eligible for State College]+[Eligible for State College and Scholarship]-[Min Distribution]*5<0,BLANK(),[Not Eligible for State College]+[Eligible for State College]+[Eligible for State College and Scholarship]-[Min Distribution]*5)

Here is what our chart looks like with the new formulas and the bar distribution field on the Y-axis:

Infographic Post Part 3 Raw Bars

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.

Infographic Post Part 3 Chart Final

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.

Infographic Post Part 3 Final with Excel Boarder

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.

Infographic Post Part 3 Compare Schools

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!

Infographic Post Part 3 Compare Years

You can download example workbooks here:

Download Excel 2010 Example

Download Excel 2013 Example

I hope this has been informative. Please comment below if you have any questions and let us know if you thought this was helpful.