How to compare your progress towards the target in Tableau using a Deviation Icicle chart?

Often, professionally, our goals are turned into targets. Even at organizational level, companies and departments of any kind set targets or goals that they aim to achieve every year. Throughout any fiscal year, managers, team leaders or decision makers analyze their organization’ activity linked to their objectives. It is important for them to be able to observe how the activities carried out during the year are reflected in their targets. In other words, they need analysis to help them identify their target values, but also the actual values. Today we will discuss about a new type of chart in Tableau that allows us to visualize our expected values and the movement of the actual values, in relation to our target. Thus, we will create a deviation Icicle chart.

In another post we discussed how we can track our performance in relation with our targets. In that post we create a Bullet Chart, which is useful when we want to identify the distance between our results and objectives. However, this type of chart is limited in terms of information provided. For example, a Bullet chart does not provide us with data about actual values and expected values, but only about the progress made towards achieving our objectives.

How do we build a Deviation Icicle chart?

A deviation Icicle chart is often used to analyze actual values (achieved performance) in relation to expected values (set targets). This type of chart is useful when we want to identify and visualize how high or low the current values are, compared to those expected. A divergent Icicle chart helps us extract more perspectives on progress and performance, as opposed to a bullet chart or a simple bar chart with reference lines. Often reference lines or a divergent line / bar chart make it difficult for important information, such as periods when current values were above or below the target, to be observed.

Thus, a deviation Icicle chart helps us observe exactly which were the periods of time in which the current performances exceed or decrease compared to our target and their exact values. In the video below, we will show you how to build a deviation Icicle chart. Also, all the steps you need to go through to build it are mentioned below in the video.

Step 1: Connect to data

→ In Tableau Desktop, connect to to Tableau: Sample-Superstore.

Step 2: Create the visualization

→ Select Analysis > Create Calculated Field: MAX Date
{FIXED :max([Order Date])}

→ Select Analysis > Create Calculated Field: Prevoius Year
IF datetrunc(‘year’,(DATEADD(‘year’,-1,[MAX Date])))=DATETRUNC(‘year’,[Order Date])
THEN [Sales] end

→ Select Analysis > Create Calculated Field: Current Year
IF DATETRUNC(‘year’,[MAX Date]) =DATETRUNC(‘year’,[Order Date])
THEN [Sales] end

→ Select Analysis > Create Calculated Field: Count distinct of customer orders
{FIXED [Customer Name]:COUNTD([Order ID])}

→ Select Analysis > Create Calculated Field: Above/Below color
IF sum([Current Year])-sum([Prevoius Year])>0 then ‘Above’ else ‘Below’ END

→ Drag Month(Order Date) to Columns

→ Drag Prevoius Year to Rows

→ Drag Measure Values to Rows

→ Drag Measure Names to Path

→ Right-click on Measure Values from Rows and select Dual Axis

→ Drag Above/Below color to Color

By Eduard Arhire

Download Tableau Software