8 Replies Latest reply on Dec 15, 2011 9:50 AM by Dave Cohen

# Combining Bar Graphs and KPI's on one worksheet

Hi

Does anyone know if it is possible to combine a horizontal bar graph and a KPI indicator graph on the same worksheet.

I'm trying to replicate the graph on the attached 'ideal-graph-layout' on one worksheet - as per Stephen Few examples -  ie. you have the bar graph, Indicator if outside of certain limits and the actual value.

I can create the two graphs on separate worksheets - and place on a dashboard but am concerned that when data is expanded/contracted that they will become out alignment.

Ideally I would like to have the two graphs on the worksheet so that they stay aligned - I've tried several different ways but can't quite get it to work (see attached workbook).

If anyone can advise if they know how to do this, or if they don't think it's possible.

Many Thanks

Ian

• ###### 1. Re: Combining Bar Graphs and KPI's on one worksheet

I calculated the position to show the KPI as 10% beyond the highest utilisation (rounded to the nearest 10%).

I made it a dual axis chart, with the axes synchronised and then I used a few tricks that came up in this thread the other day to blank out the second axis.

• ###### 2. Re: Combining Bar Graphs and KPI's on one worksheet

Thanks Richard - that's great

I'm glad I asked the question because I would never have thought of doing that.

I'm struggling to replicate exactly what you have done - when I put the KPI idicator and bar I'm having issues with either, the dual axis showing at the top or it not lining up correctly (as if the KPI Position is not using the same scale).

I'll have a check through the forums for dual axis info - but if you could point me in the right direction that would be great.

Ian

• ###### 3. Re: Combining Bar Graphs and KPI's on one worksheet

You need to tell Tableau to synchronize the two dual axes.  You do that by right-clicking on the second axis (the top one in this case) and selecting the synchronize option.  It took me a while to work out why I couldn't do that when I was putting that together - I kept clicking on the bottom axis, and the synchronize option is greyed out there.

(Thinking about it - I would have thought it really ought to be available from both - after all the operation is synchronizing them with each other, so it doesn't seem to make any logical difference where you select it from.  Anyway, that's how it works now.)

• ###### 4. Re: Combining Bar Graphs and KPI's on one worksheet

Hi Richard

I've got them Synchronized.

I'm sure this is really simple (and feel stupid for asking but just can't work it out:) how do you then hide (not show) the top axis ?

I tried to go through the thread which you referenced on your original response - but alot of the attachments aren't available to view so can't quite make it out what was done.

Thanks again

Ian

• ###### 5. Re: Combining Bar Graphs and KPI's on one worksheet

The only stupid question is the one you don't ask - the whole point of these forums is so we can help each other out - so just keep on asking.  ;-)

It was definitely a bodge to hide the top axis.

Right click on the top axis and select Format.

On the format pane, the scale section has Ticks and Numbers.  In the drop-down for Ticks select None.  For the Numbers drop-down, select Custom format and enter a single space in the format mask input box.  This tells Tableau that whatever the number is, just display a space.  See what I mean about a bodge?

• ###### 6. Re: Combining Bar Graphs and KPI's on one worksheet

OK - got it.

For the life of me I couldn't see the difference between your worksheet and the one I tried to create.

It might be a bodge - but it's an elegant bodge :-)

• ###### 7. Re: Combining Bar Graphs and KPI's on one worksheet

Same material can be found at Purchasing KPIs. I hope it's useful for you and you like it. Please continue sharing more information at this topic.

Best rgs!

• ###### 8. Re: Combining Bar Graphs and KPI's on one worksheet

Ian,

Attached is another possible display - move the KPI Indicator in front of the bar chart - I used the same approach as Richard, but hard-coded a value of -.06 for the calculated field KPI Position.  I then fixed the range of the axis to be -.12 to 1.2.  And, since staff utilization is typically in the range of 50% to 120%, I removed the display of both the top and bottom axes.

This approach is less flexible than Richard's since a utilization value *could* be greater than 120%.

I also added a parameter control for the utilization target so that moving the threshold up or down will change the utilization % at which the indicator displays.

Dave

Navcentric

www.navcentric.com