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.
One of the great things about Tableau is that you can get the same solution by “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.
Let us create our view by dragging an order date on Rows and “Profit” on Columns. You can opt a monthly view by writing an ad-hoc calculation on Rows shelf DATEPART(‘month’, [Order Date])
Create a Parameter with an Integer data type as per the below image and put it into on Detail shelf.
The simple trick to solve this Problem is to force the aggregated measure into two sections.
- One bar section should be below the Reference line
- 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.
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
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.
Leave a comment
You must be logged into post a comment.