Bar charts on Dashboards

[DashboardNovice]

I am working on Part 4 of 6 on the KPI dashboard here:
http://chandoo.org/wp/2008/09/10/kpi-dashboards-graphs-excel/

I am stuck on steps 2 and 3 involving the creation of bar charts, adding the black vertical average line.

In step 3, there is a reference to Jon Peltier's website:
http://peltiertech.com/Excel/Charts/BarLineCombo.html

1. On Jon's website, what is the purpose of the "placeholder" column in column H?
2. I used the table just as it appears and I followed the steps exactly as specified. The first chart turned out ok. But the second chart I tried to create (where you copy and paste Brand 1 data which appears in burgundy colored bars) does not have the smallest value (0.5) for Attribute 1 and the largest value (4.5) for Attribute 5 like Jon's website has.

Instead, the chart I ended up with had the largest value for Attribute 1 and the smallest
value for Attribute 5. This appears to have something to do with the descending sort with
the placeholders (column H) or with selecting "Categories in reverse order" when creating
the first chart which contains values for Attributes only. Why did my values for the
burgundy bars turn out to be "inverted."
​

3. Back to Chandoo's website, the first sentence of step #3 says "add an average line to each
of the bar charts by using an XY-scatter chart type." Does this mean taking an XY chart
and overlaying it on top of the bar chart?
4. In step #5 the second paragraph reads "To avoid this, add two additional XY-scatter-
series to the chart, representing the minimum and the maximum of the total data and
assign them to the secondary axis. Furthermore add 2 additional bar series to the
chart, again representing the minimum and the maximum of the total data and assign
them to the primary axis."

• • • Does this mean that the final result will contain a total of 5 charts (3 bar charts and 2 XY scatter charts)?
    • If there are a total of 5 charts, are they somehow merged together or does this involve having 4 charts sitting on top of the original bar chart?

​

 

[Luke M]

From Jon's site:

1. The {4.5, 3.5, . . . 0.5} values in column H below the blank cell are placeholder values which will be plotted on the vertical axis against the Brand 1 to 5 values which will be plotted on the horizontal axis. These placeholder values are in decreasing order here, because I am going to plot the attributes in reverse order.

These values are needed for the XY series. In the step where Jon add's Brand 1, you'll see that we only select the Brand column and the placeholder. The Brand column controls the x-axis, the placeholder controls the y-axis. Since we're using a bar chart, the bars at placed at the 1/2 marks, aka 0.5, 1.5, 2.5, etc.

2. As for why it didn't work, w/o seeing your chart, I would suspect either the "plot in reverse order" checkbox didn't get checked, or your values in placeholder column are reversed.

3. No, it's an XY series in the same chart. This goes back to the technique shown in #1, which is why Chandoo referenced Jon's site. It's trickers to add a XY to a bar cart than a column chart, but Jon shows us how.

4. No, each KPI has a single chart. Each chart will actuall have 7 series.
Black Bars (Jon's conditional formatting)
Red Bars (Jon's conditional formatting)
Average (Bar-Line Combo)
Min XY (Step 5 of Chandoo)
Max XY (Step 5 of Chandoo)
Min Bar (Step 5 of Chandoo)
Max Bar (Step 5 of Chandoo)

Sounds like you are around step 2. You need to check out Jon's tutorial on conditional formatting, and create a chart with Black bars and Red bars. Then, go through the Bar-Line combination tutorial to figure out how to add the Average line. You'll then be done with step 3 of Chandoo's tutorial.

remember, if you need help, there's a downloadable workbook at the bottom of page you can get to see how Chandoo did things.

 

[DashboardNovice]

From Jon's site:

These values are needed for the XY series. In the step where Jon add's Brand 1, you'll see that we only select the Brand column and the placeholder. The Brand column controls the x-axis, the placeholder controls the y-axis. Since we're using a bar chart, the bars at placed at the 1/2 marks, aka 0.5, 1.5, 2.5, etc.

2. As for why it didn't work, w/o seeing your chart, I would suspect either the "plot in reverse order" checkbox didn't get checked, or your values in placeholder column are reversed.

Thank you for your reply. I have an attached workbook where I tried to work out Jon's solutions in steps 2 and 3 (tabs 1 and 2). The website link is at the top of each tab.

I have questions at the bottom of the first tab, and also I column L of the second tab.

Any help from you (or anybody else) would be greatly appreciated. I'm just seeing things that I can't make sense out of. I might be overlooking something too.

 

[Luke M]

Hi DashboardNovice,

See attached file, where I tried to answer your questions.

 

[DashboardNovice]

Hi DashboardNovice,

See attached file, where I tried to answer your questions.

Hello Luke M. I took a look at your attached file that answered my questions. Thank you very much for taking the time to do this. Your answers were very clear and helpful.

I clicked "Like" under your post. Thank you very much.