12 Dec How to Normalize Data Point Axis in Tableau
In this article, I will show you how to normalize data point axis in Tableau. Recently, I got a request to build a Panel chart of all the measures in a data set and I came up with something below (I built this with a sample superstore);
You can see above that the discount and Quantity line chart looks flat and this was because they share the same axis with Units and DV that have pretty big axes. This is not too good because it is very difficult to see trends in both Discount and Quantity.
Request: Plot each measure to clearly show their trend.
Proposed Solution: I came up with the mathematical solution below;
- Pivot all the measure fields, this is done to have all the measures plotted in one panel
- Pick up each data point per date (in my case monthly)
- Pick up the Maximum value of all the Measure field
- Sum the measure field per pivot field (Measure name)
Mathematical expression;
Normalize X = (Xi * MAX (Xi-n)) /∑X (i-n)t
Where Xi == b
MAX (Xi-n) == c
∑X (i-n)t ==d
Note that I came up with this formula and it is not a standard formula for Normalizing data
Tableau Desktop
I will not go into the details of how to create a panel chart in Tableau. This is how my data looks like after pivoting;
- Pick each data point tagged ‘Data point’ with the formula below;
{ FIXED [Pivot Field Names],DATETRUNC(‘month’,[Weekly Start Date]):SUM([Pivot Field Values])}
2. Find Maximum value from all the data point, tag this ‘Max’
{MAX( { FIXED [Pivot Field Names],DATETRUNC(‘month’,[Weekly Start Date]):SUM([Pivot Field Values])})}
3. Estimate sum Pivot value per Pivot Name, tagged ‘Sum of Metric’;
{ FIXED [Pivot Field Names]:SUM([Pivot Field Values])}
4. Finally, compute the Normalized value for each data point using the formula provided above;
([Data point] * [maximum])/[sum per metrics]
Below is how the panel chart looks after normalizing both the Discount and Quantity line trend.
Check out the dashboard on Tableau Public.
Link to the dashboard
No Comments