Overlapping Series in Cluster Chart?

[jetsfan15]

Hi - I have a question which I am sure as a workable solution, but I am struggling to figure it out. I currently have the attached stacked cluster chart (that dual axis is a separate counter from the $ values in the bars themselves - that line can be ignored).

The way I currently have it set up is a stacked column chart where I am essentially breaking it up into "parts" with the assumption that the light blue value will always be the highest, followed by the middle blue, followed by the dark blue (meaning this dark blue is always the lowest).

This assumptions works now, but it is possible that down the line the middle blue might actually be higher than the lightest blue. As a result, I want to figure out a way to overlap the series in a regular cluster chart, but somehow set it up in a way so that the smallest value will dynamically appear at the front, followed by the second smallest, followed by the last and largest number (which will "peak out" above the other two. Of course, this would mean using the "actual" item values, rather than breaking it up into pieces as I currently have it set up.

I am familiar with overlapping series, but is there a way to set a "priority" of which of the values should show the front most (which I always want to be the smallest value, and progressively up from there).

I hope that all was explained clearly, and if not, of course happy to rephrase or send additional attachments.

Cheers!

[Andy Pope]

You can only set the priority of a whole series. so Light in front of dark series. You can not do it on a category by category item.

You can mimic that action by having multiple series for each layer. So you have 3 layers Front|Middle|Back. and within each layer you have a colour Dark|Medium|Light.
Use formula to determine which Dark|Medium|Light. point to populate for each Front|Middle|Back layer.

[jetsfan15]

Hi Andy - quickly coming back to this, but how would I set the priority of the whole series?

In other words, let's say I know Series 1 would always be the smallest, Series 2 in the middle, and Series 3 the largest value.
But, I still would like to visually chart there whole values with Series 1 in front, then 2, then 3? - while still plotting their actual values?

Rather than the way I currently do it with a stacked column chart, where essentially I say:
1) Chart Series 1
2) Chart the difference of Series 2 and Series 1 (using dummy column)
3) Chart the difference of Series 3 and Series 2 last (also using a dummy column)

[Andy Pope]

You can set the priorty in 2 ways.
either in the layout of the data or use the Select data dialog to alter the plot order, using the up/down arrows.

The higher the plot order the closer the series is to the front, in terms of layers.

so the data layout would require the tallest series to be first continuing in des3cending order