Public Health Performance Benchmarking Dashboard

As dashboard design professionals, we should all “get our hands dirty” in each other’s dashboard projects when possible. Here’s access to the world of public health dashboard projects. Think you have stringent documentation requirements in your organization? Try layering on the accountability that comes from spending public dollars on public health programs.

This pdf shows some of the workflow behind a recent performance benchmarking project for a Los Angeles County program related to Outpatient Counseling Programs.

3 sample performance metrics dashboards are shown.

County of Los Angeles Public Health Dashboards

KPI Dashboards in Excel

This KPI Dashboard is created using Microsoft Excel 2007 by Chandoo as part of his Dashboard Week series. Note that this exact dashboard is featured as a lesson in the dashboard training module of Excel School, so if you want a detailed step-by-step account of its design and construction, click on this link: Excel Dashboard Training.

Here is the KPI Dashboard:

If you would like to download this excel dashboard, use this link:

Download Excel Dashboard: KPI Dashboard Example

From Chandoo’s description:


Changes to the KPI Dashboard:

I have made the following changes to the original dashboard:

  • Added a top bar where we show top 3 products in each KPI
  • Added ability to restore to original sort order (as per the input data in Data sheet)
  • Instead of showing triangle arrows, used conditional formatting arrow icons – green for values >=90th percentile and red for <= 10th percentile for any given KPI.
  • Added individual KPI targets by product (instead of same KPI targets for all products). Also, changed the bar chart visualization to show target markers.
  • Added ability to switch on/off the target indicators.
  • Added a KPI distribution chart and ability to search by any product.

Changes to the KPI Dashboard - Excel Dashboards

How are these changes made?

Restoring original sort-order:

For this, I have used the product numbers (values 1 to 100) in Data sheet and sorted them on ascending order. When you click on the product column’s sort button, in the background I just use the product numbers column to sort the KPIs.

Percentile Indicators:

This is the same technique as alert icons in dashboard. Just that I also showed green icons.

Turning on / off the KPI target indicators:

Based on the check-box setting, I return #N/A (thru NA() formula) or actual target value to the chart’s source data range. Rest of the puzzle, you can figure out.

The technique is also explained here: Dynamic Excel Chart with Checkboxes.

Search by Product & Highlight KPI values:

For this I have used an active-x text box and linked it to a cell (L22). Then, I used COUNTIF with wild-card search to locate if a product matches the input or not. [More on the wild-card search technique]

KPI Distribution Chart:

This is an area chart, re-sized to fit inside the space. The red-lines are y-error bars and they are drawn for products that match the search criteria.


If you are interested in using Excel for Dashboards, click on this banner to learn more about Excel School for Dashboards:

Excel School for Dashboards

Google Panda Algorithm Update

Several Dashboard Spy readers wrote in asking about a drop in their site visitor stats from the Google search engine late last month. It turns out that with some digging through their Google Analytics stats, we found that they were in fact hurt by the recent Google Panda/Farmer algorithm update.

Take a look at this screenshot of a Google Analytics account. It shows the traffic segmented into US traffic from only Google search engine. Note that on February 24th, the date of the algorithm change, there was a significant drop in referrals from Google. This clearly indicates that the site was hurt by the update.

For exact, step-by-step instructions on how to tell if your website has been affected by this update, see this very informative article:

Google Panda Update Survival Guide

Excel Dashboard for Customer Service Metrics

Chandoo and his Excel Dashboard programmers are having a great time during their “Dashboard Week”. They are building and sharing Excel-based dashboards and I’ll be sure to report to you some of the great work they are doing.

(Chandoo runs the Excel School which includes modules for Excel Dashboard Training – click the link to learn more).

Today we share this excellent excel dashboard example from Mara.

Here is the excel workbook link for this dashboard: Excel Dashboard for Customer Service.

Here’s a look at the dashboard itself:

Excel Dashboard with Customer Service Metrics

Here are the comments from Chandoo:

Techniques used by Mara to Create this Dashboard:

Mara used several techniques to create this dashboard. But I specifically liked 5 things about this dashboard. They are,

  1. Tweetboard kind of area at the top where she showed summary of status.
  2. Dynamic dashboard which can be filtered based on a month.
  3. Interactive chart with check boxes to show / hide information.
  4. Interactive comparison chart to compare target with actual performances (of valet wait times).
  5. Scrollable list of various gift shop items.

See the numbers on this image for reference:

customer service dashboard in excel

Again, this is the excel workbook link for this excellent excel dashboard: Excel Dashboard for Customer Service.

If you are interested in using Excel for Dashboards, click on this banner to learn more about Excel School for Dashboards:

Excel School for Dashboards

Green Building Studio Dashboard

Architects and building designers use a web-based energy analysis software called Green Building Studio to calculate carbon footprint costs, compare the carbon neutrality benefits of different design choices, perform whole building analysis and optimize energy efficiency.

The product is made by Autodesk and just a few months ago, the added a Green Building Studio dashboard into their software to make it easier for designers to compare different projects against each other. The building dashboard is reported to be especially useful for building owners to compare a portfolio of buildings.

Here are two dashboard screenshots that show how comparisons can be made either in chart view or list view:

autodesk green building studio dashboard

The above screenshot is the graph view with Energy Use Intensity and Annual Energy Consumption as the chosen metrics.

Here is the list view of Potential Energy Savings:

autodesk green building studio dashboard list view

Here is further information on the Green Building Studio Dashboard:

building energy savings metrics

green building metrics

Are Federal Data Dashboards Inaccurate?

The Dashboard Spy welcomed with much fanfare the Federal IT Dashboard and the sites.

Now, in a stunning development, the accuracy of the data on these federal dashboards is in question. Accusations are being made that the information is faulty.

As all of us business intelligence dashboard project implementors know, the question of data quality fundamentally calls the entire dashboard project into question.

Take a look at this headline from Information Week Government:

federal data dashboard inaccurate

This from the article: Watchdogs Criticize Federal Data Dashboard Accuracy

Read this carefully – it’s enough to give a dashboard project owner a total heart attack. The data on the dashboard is being called “almost entirely useless”.

The Obama administration has backed up its plan to boost transparency by releasing heaps of federal data via its open-data push. But both federal and independent watchdog agencies are criticizing the administration for inaccuracies they’ve found in at least two of the federal government’s online data repositories.

The Government Accountability Office (GAO) found numerous errors on the Federal IT Dashboard concerning data about federal IT investments that the Office of Management and Budget (OMB) uses to track investment changes and identify performance issues, it said in a report (PDF) released online Tuesday.
Separately, The Sunlight Foundation, an open government advocacy group, performed an analysis that found nearly $1.3 trillion of federal spending as reported on the Web site is inaccurate. Sunlight Foundation co-founder and Executive Director Ellen Miller testified before Congress last week about the findings of the organization’s Clearspending analysis of the site.

The GAO report took a look at ratings of selected IT investments on the IT dashboard to see if they accurately reflected the performance of the project they represented. Specifically, the agency found inaccurate cost ratings for six of the investments and inaccurate schedule ratings for nine.

For example, the dashboard rating for a Department of Homeland Security investment reported significant changes in the cost of the project for 3 months in 2010. GAO analysis of the project, however, showed less variance in cost during the same time period, according to the report.

The agency cited “weaknesses in how agencies report the data to the dashboard, such as providing erroneous data submissions, as well as limitations in how OMB calculates the ratings” for the misinformation presented on the dashboard.

“Until the selected agencies and OMB resolve these issues, ratings will continue to often be inaccurate and may not reflect current program performance,” the GAO said.
The Sunlight Foundation’s analysis focused on another key open-data site, the Federal IT Dashboard, which posts financial and other data about current federal IT projects.

Miller’s appearance last week before the House Committee on Oversight and Government Reform’s Subcommittee on Technology and Information Policy is not the first time she has been critical of At the Gov 2.0 Summit last September, she called data on the site “almost entirely useless” because of flaws.

This is stunning, isn’t it?

Here’s the GAO report link: Information Technology: OMB Has Made Improvements to Its Dashboard, but Further Work is Needed by Agencies and OMB to Ensure Data Accuracy

federal dashboard data accuracy

Hubert Lee
The Dashboard Spy

Japan Earthquake and Tsunami Infographics

Our thoughts and prayers go out to all affected by the earthquake and tsunami in Japan. Please, we encourage all Dashboard Spy readers to help the relief effort if possible. Visit to find out how or text REDCROSS to 90999 to send $10 to support disaster relief efforts in Japan.

The disaster spawned many infographics. Here is a sampling being distributed across the web (sorry if the attribution is lacking – with all the sharing going on, it’s hard to tell the original creator of these infographics)

New York Times Interactive Map of the Damage from the Japanese Earthquake
View at

Japan Earthquake Damage Map

New York Times Tsunami Graphic of Predicted Wave Heights
See more maps at

Japan Tsunami Predicted Wave Heights

Walll Street Journal Interactive Graphics: Reactor Monitor (view using that link)

Wall Street Journal Interactive Graphic Japan Earthquake Reactors

Facebook Infographic (showing reports of the words Japan, earthquake and tsunami over time)
Facebook Japan Earthquake

Tsunami Travel Time Infographic
japan tsunami travel times infographic

Apple Home Energy Management Dashboard

The Dashboard Spy has been reporting the emergence of home energy dashboards (see Home Energy Dashboard and Home Energy Monitoring Dashboards).

Well, it’s being reported that Apple has an upcoming Smart Home Energy Management Dashboard System.

Take a look at these images and then I’ll send you a link to the report.

Here is the shot of what looks to be an apple-style home energy dashboard:

apple smart home energy dashboard

And here’s a very interesting look at the controls and metrics that the Apple Home Energy system will feature (It’s an illustration from Apple’s patent application):

apple home energy dashboard

Here’s the link to the fascinating story from

Apple Reveals Smart Home Energy Management Dashboard System

Here’s a clip:

Every once in a while we’re given powerful insights into systems that could be game changers – and while not apparently sexy at the moment, Apple surprises us with a peek at their coming Smart Home Energy Management Dashboard System that packs a punch. Apple’s patent reviews technology related to this system that many simply know as HomePlug Powerline Networking. HomePlug Powerline Networking turns every power outlet in your home or office into a conduit for audio, video and data. Wireless technologies could be prone to dead-spots and fading – but with HomePlug certified adapters you just plug them in and within minutes you have high speed internet coming out of every plug in the house. You could do the same thing for HDTV and iTunes. Get ready folks, because this looks like Apple is ramping this up for sometime in the near future. And, let it be said, could be yet another tablet application.

Did you catch that? Did the author say the dashboards are NOT sexy at the moment? Obviously he doesn’t read The Dashboard Spy!

No Nonsense Color Guide

Dashboard Spy Resource Tip: The No Nonsense Guide to Using The Right Colors. (Via Kajabi)

This handy guide to color usage comes from the folks at Kajabi. They claim that research reveals that 93% of our decision-making comes from how something looks. 85% of that group say that “color” is the reason they chose a certain product.

I find these guidelines to be a good summary of color characteristics.

Check out the graphic below.

If you want a higher resolution version for your reference, use this link:

The Meaning of Colors

no nonsense guide to using the right color

Name Your Software Application

I’ve had my share of fun naming business intelligence applications for major companies. You’d be amazed at the lengths I’ve gone to in creating the right kind of brainstorming environment for these exercises. I’ve named huge products at companies like MetLife, Pfizer and a host of others.

Let me share a couple of tips and give you a fun “name generator” to stir your creative thinking when it comes to naming your business intelligence software application.

First, here’s an excerpt from an article called “How to name a Software Product”

The importance of choosing the right name for software is not to be taken lightly. The name of your software is an important part of its “business card”. It is the hand that reaches out to a prospective customer, and it is up to (metaphorically speaking) this handshake and how the prospect feels about it that purchases will be made and the “ka-ching” sound will ring joyfully in your ears.

Choosing a good name is particularly important for a number of reasons, such as:

  • Your software will act as your spokesperson, introducing you to various people;
  • May be the first thing prospective customers find out about you (from magazines, search engine results, etc.)
  • It is a good way to differentiate yourself from your competition;
  • It is the name that your product will carry for a number of years, so you have to make the right decision in order to avoid more costs with re-branding, letting your established customers know about it, etc.;
  • It is a good means of making your business’s name appear in the search engines, and thus attract prospects.

Here’s the fun generator I mentioned:


Click on the link above to give it a spin. Here’s a screenshot of something I came up with.

dotomator name generator

More tips from the article:

Brainstorming: The right name will not pop up right away. It will take some time until you come up with a name that you like, so all ideas should be written down. Make sure you take into consideration even more “exotic” names and that you do not reject from the start names that you would regard as inappropriate. Any suggestions should be put down. Once the list is done, start brushing it up. Experts say that words that start with consonants are the best for such issues. Also, the shorter the words, the better.

Use various linguistic tools: Don’t neglect thesauruses, dictionaries. Look for synonyms of the words that most appeal to you.

Invent: Be as creative as possible. Make use of your imagination (and of that of your team members) to come up with original names. One way to draw creativity is to show them pictures from any domain and to ask them to suggest names in accordance with the theme of that picture.

Create word pairs: Use the list that you have come up with earlier in the process, and try to see which words look good in conjunction with others. Anyway, the general opinion is that one-word names are the best.

Stay legal: Perform a search to see if any of the names you came up with (and you really like) is already taken and registered as a trademark. You wouldn’t want to be dragged through courts in endless, costly lawsuits.

Search for competition: Perform a search via search engines (use as many search engines as possible, not just Google and Yahoo) to see what competition you have for your selected names. Discard those that might cause confusion.
Easy to pronounce, easy to remember: Make sure that most people can pronounce the name you choose easily. If they can pronounce it, they will be able to recommend it to others as well. Word of mouth is as important as ever in such cases too.

Mind the translation: If you plan to distribute your software on particular foreign markets and you choose to translate its name, make sure it sounds right in the target language as well. Try to avoid terms that might cause confusion or have offensive meanings in other languages.

Select the name of the software’s web site carefully: If you decide that you’re going to create a separate site for your software, then make sure you reserve the right domain. Make sure that it does not sound awkward, that it will not be misread, and that it doesn’t have an obscure ending (the most common extension for sites is “.com”).

Don’t alienate customers: It is very important to check if the name of your software (or part of it) may be considered offensive for certain people (your software might imply political orientation, race, religious orientation, gender negative connotations).

Analyze: When you think you’ve reached the end of the naming process, don’t just stop and attach the name to your software. Test it on employees, on friends, on reliable customers. See what feedback you get. Also, check that the name you’ve chosen really says what you wanted it to say.