Sometimes when you want to compare 2 metrics in your visualisation but somehow they are too different? For example in the Superstore data set there are Profit Ratio and Quantity. One is in percentage and one is absolute value. If you put these 2 metrics into 1 chart it will look like this:
(This is comparing Profit Ratio to Quantity by the level of States)
All the dots in Profit Ratio will be barely visible as Quantity are having much higher value than that of the ones in Profit Ratio which are all sitting between 0 and 1. And this is where a z score will come in handy.
What is a Z Score?
As a person who can put "limited working proficiency" in the language of Statistics in his Linkedin Profile. I will try my best to explain Z score: so a Z score is describing a value's relationship with the mean(in Tableau term: average) of the data, as in the how much the value differ from the standard deviation. As intimidating as it sounds, it is actually not that hard to create Z score in Tableau. So here's how:
- Use WINDOW_AVG() and WINDOW_STDEV() as below
Create the calculated fields as below:
You do not need to worry about the Tableau calculation configuration for now. One minor note is that you can see there is a SUM() function called before every Quantity field but not for Profit Ratio. The reason why is that Profit Ratio is already a calculated field (it is Profit divided by Sales in case you do not have it with you). So you do not need to call another aggregation function for it.
2. Replace the original values on the view with the Z score
When you have replaced the values with the Z score there is a chance you might have a blank chart with bunch of nulls. If that happens you will need to configure the table calculation of the Z score. Go the "Edit Table Calculation" on both fields and select "Specific Dimension", and tick the level of details you want to have. In this case: States.
After the configuration your view should look something similar like the one above. The axis of the Z Score should be within 4-5. As they are standard deviations and the values and each dot is indicating how far they are from the mean of the data set.
The benefit of doing a Z score is that you can easily spot an outlier across metrics even they have various range of values. And it gives you the option to communicate the data in a more statistical way without the need to consider how different each metrics are.
So here is how you create Z score calculation on Tableau. Hope you find it useful:)