4 Replies Latest reply on Jun 25, 2018 10:47 AM by Ken Flerlage

Combined Diagram

Hi,

I am trying desperately to create a diagram chart that will show the number of city that has a certain number of population within a certain area.

For example, the number of cities that have between 200-190k ppl, then the number of cities that have between 190-180k,...

The issue is that few segments can have the same number of cities. For example, segment 200-190k and 80-70k can both have 10 cities with this respective population. I was thinking to combine diagrams.

On top of this, I would like to calculate the number of cities that correspond to 80% of the total population in this area.

I know this is pretty complicated to explain with words, so I draw a graph that corresponds to what I need. If anyone know, or have an idea to how create such a graph, I would really really appreciate.

If you have a better idea to display this data, I am also very opened to suggestions.

See here under a sample of my data:

 Total population per city Count of cities with this population 190 1 185 1 180 1 175 1 170 1 165 1 160 1 155 1 150 1 145 1 140 1 135 1 130 1 125 1 120 1 115 1 110 1 105 1 100 1 95 1 90 1 85 1 80 1 75 2 70 2 65 4 60 6 55 4 50 8 45 10 40 10 35 15 30 27 25 36 20 89 15 158 10 523
• 1. Re: Combined Diagram

I think this is an ideal use case for a histogram. The following histogram shows your data broken into bins of 10K. See the attached workbook.

The best way to do the 80% line would probably be a reference line. Unfortunately, out-of-the-box histograms do not allow reference lines. But, if you're interested, there are some hacks to get around that.

If this assists in resolving your question, please mark my response as the 'correct answer'. This will help other users with similar questions. Thanks!

• 2. Re: Combined Diagram

I appreciate your prompt reply. However what I am looking for is the reverse: the number of population per city over the number of cities.

Sorry if it was not clear, I draw another graph that might be more clear.

• 3. Re: Combined Diagram

I don't think you can show this with bars and have it be usable because there is so much overlap in the "1" column. Not quite sure what you are trying to do with this view but using circle marks may get you what you are looking for:

if this is still not quite what you need, maybe you can explain more of what you are trying to show/what story you are trying to tell here. Someone might have an idea of a slightly different way to approach the problem

Hope this helps!

• 4. Re: Combined Diagram

Right, agreed. That's why I was thinking that an alternate approach might be better. Again, I think that a histogram is a really good way to show this data, even though it's not exactly what is being requested.