Defining a custom color palette for BI Dashboard bar charts

Out of the box, the simple bar graph uses the same color for each bar.

A frequent request from users is to define a standard color palette for the dashboard indicators so distinct bars are the same color in each indicator or from refresh to refresh. With most dashboard tricks, like the dynamic dashboard range, it is all about the data. The steps are to create a numeric value for each bar's categorical variable (such as region), create a range that matches these numeric values and defines the color for each, and the use a range enabled bar chart to leverage these colors but still display the bar's numerical value (such as sales total).

Let's walk through this in more detail.

Step 1. Set up the data

For this example, I used a SQL query to pull in the total sales for three regions in the sashelp.shoes data set. Then with a SQL case expression, I added a 'color' column where 1 is for Africa, 2 is for Asia, and 3 is for Canada.

The important change to note is that for the data mapping tab, the color column will be changed so that the value displayed is the total sales. This means that the graph will leverage the color to plot, but the actual data value shown to the user will be the total sales.

Step 2. Define a custom color range

Create a range that defines which colors to use for which corresponding number. In the example screenshot below, I chose to have Orange for Africa (or defined as 1 in the data), Blue for Asia (stored as 2), and Red for Canada (and marked as a 3).

Step 3. Use a bar chart that leverages range

The final step required a bit of exploration, but what turned out the best was the use of the "Bar chart with bullet" indicator. I used the data and range defined in the prior steps, and then specified that the 'color' value was the range (#1 in the screenshot below), the 'region' was the x-axis value (#2), the bar value was 'color' (#3) and the bullet value is 'total_sales' (#4). You'll notice in the chart the y-axis label states 'color(millions)'. Just add the term 'Total Sales' to the y-axis title field to correct this as well.

tags: BI Dashboard

5 Comments

  1. Posted September 13, 2012 at 11:10 am | Permalink

    Very clever. I was talking about designing dashboards yesterday.

    • Angela Hall Angela Hall
      Posted September 13, 2012 at 11:23 am | Permalink

      Like usual, we are on the same wave-length Tricia! :)

  2. Frédéric Wéverberg
    Posted September 18, 2012 at 5:34 am | Permalink

    Very helpful Angela !

  3. Angela Broach
    Posted September 18, 2012 at 1:30 pm | Permalink

    I wasn't able to get these results using a bar chart with bullet, but using a targeted bar chart worked very well with the target set to (none)

    • Angela Hall Angela Hall
      Posted September 18, 2012 at 1:41 pm | Permalink

      All bar chart types that allow for range selection will work for this example. Either "bar chart with bullet" or "targeted bar chart" will work. The trick is to ensure that your selections for display setting correspond with the data appropriately.

      For "bar chart with bullet" note that the following were selected:
      range value = color
      x-axis value = region
      bar value = color
      bullet value = total_sales

Post a Comment

Your email is never published nor shared. Required fields are marked *

*
*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <p> <pre lang="" line="" escaped=""> <q cite=""> <strike> <strong>