Aug
1
2018

Highlight the bar above the Reference Line

Sometimes we have a requirement of coloring our bar chart depending on some reference value. Now if you highlight the bar chart above reference line, it suggests that the bar is composed of two parts instead of just being a single bar.

Recently I came across a very similar requirement where I need to highlight the bar above the reference line. This is a very simple but yet an effective requirement. While Googling , I found a very interesting website of Tableau Community Expert Keith Helfrich where he explained a very similar concept in one of his blogpost. Please refer the below link.

https://redheadedstepdata.io/color-the-tips/

One of the great things about Tableau is that you can get the same solution by http://homescope.ca/morrisroe-is-my-favourite-neighbourhood-in-red-deer-alberta/?ak_action=reject_mobile “N” number of ways.

What are the possible solutions that come to your minds before getting to the final Solution? Let me tell you my thought process.

  • We can build a stacked bar whose two segments (dimension used) together equal to the sum of total Measure value.
  • Then we can calculate the difference of Total sales with the assigned Reference value
  • Once the Bar chart is available, we can assign any desired color to it and then with the help of dual axis we can overlap the marks and get the desired output.

follow link Step 1:

Let us create our view by dragging an order date on Rows and go to link “Profit” on Columns. You can opt a monthly view by writing an ad-hoc calculation on Rows shelf DATEPART(‘month’, [Order Date])

Step 2:

Create a Parameter with an Integer data type as per the below image and put it into on Detail shelf.

Step 3:

The simple trick to solve this Problem is to force the aggregated measure into two sections.

  1. One bar section should be below the Reference line
  2. One bar section should be above the Reference line

If we convert the above-mentioned points into a calculated field.It should look like the below image.

 

 

 

 

 

Put this newly created calculation on your column shelf just beside your aggregated measure.

 

 

You will notice that the two axes have different scales. At this point, you need to decide whether or not the two axes should be synchronized.  To do this, right click on the Profit axis and select Synchronize Axis. Once you have a dual axis chart, you will be able to use different marks on each measure. On the Marks Card, you will now have the option to format the two measures differently.

Step 4:

There are two ways you can get the final output.

  • You can create a calculated field as per the below image and then add it to the color shelf and then assign an individual color to each legend

Another approach is by using the measure name on the color shelf. Firstly we need to assign a single color for Truncated Profit axis. Here I am using a Dark robin blue color.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Secondly, we need to add a “measure name” on the color shelf for Profit axis and assign a color.In my case, I am using a green color. Please refer to the below image.

 

Once everything is ready, your Final image will look like the below image.

 

Related Posts

About the Author: Rajeev Pandey

I am a multidisciplinary designer working in data visualization, interaction design and innovation. Expertise in developing Tableau, Web focus based visualization and reporting applications. I have a passion for analyzing, dissecting, and manipulating data sets as well as, building beautiful dashboard. Naturally talented in communicating between technology and business needs. Diverse and experienced in plenty of different domains .I am quick learner who can absorb new ideas and can communicate clearly and effectively.I love creativity and enjoy experimenting with various technologies.

Leave a comment

You must be logged into post a comment.