Speedometer with multiple needles

[PolarSpartan]

I'm trying to create a speedometer chart using Doughnut and Pie charts. I've got a doughnut that shows red, yellow and green performance ranges. I'm able to use a pie chart to show a needle indicating performance.

I need to show 3 needles: beginning, previous period, and current performance levels. The math is easiest if I can use an individual chart series for each of these (1 degree of arc for the needle and no-fill for the remaining 364 degrees).

This method is working for one pie/chart series, but when I layer the next one in, it is hidden behind the first, even in the 'no-fill' space. Changing the order of the series has not helped.

Any ideas how to make all this happen? The only functional method I've seen made the needles from scatter data (rather than pie charts), which involves trigonometry that the users (and I) won't be able to make sense of.

Any help would be appreciated.

 

[Chihiro]

To best of my knowledge, you would need trig and algebra for multi needle gauge chart.

And I'm of the opinion that gauge chart adds little value to data (or may even detract from it).

Bullet chart I believe is better alternative in most instances for a dashboard.
By adding spark line next to it, you can add historical context as well.

Here's tutorial on how to make bullet graphs.
http://chandoo.org/wp/2008/07/21/dashboard-bullet-graphs-excel/
http://exceluser.com/excel_dashboards/bullet-graph.htm

 

[vletm]

@PolarSpartan
Would it be something like this kind of short needles?

 

[PolarSpartan]

Thanks for the replies.

I tend to agree on the value of the gauge charts, but in this case I was overruled.

vletm, if I understood your approach correctly, you used one series for all three needles, with additional slices between them. That delivers the right output in the most common situation I'm using it for: improvement is demonstrated with each data point. If, however, the performance gets worse over time, the needles need to change order. I think this can work with some manual intervention, but I'm hoping to use multiple iterations of the graph, hopefully with no manual changes. This is why I started trying to use individual pies, so the needles could move independently of each other.

 

[vletm]

@PolarSpartan
Okay, three 'needles' ...
and with VBA it would possible to do too,
but You gotta do needed calculations.

 

[PolarSpartan]

I think that will work, hadn't considered using multiple doughnuts. Thanks for the creative solution.