How to use Z-scores in Tableau Desktop, to normalise data, and compare categories.
What data am I looking at?
For the purposes of this blog, I want to compare the price of Airbnbs' across London (borough), based on the room type (category: entire apartment, private room and shared room).
The view above shows the distribution of Airbnb prices by room type. This view tells us that renting apartments is more expensive, and a larger spread of prices. However, private rooms and shared rooms have outliers. The outlier of interest is the shared room in Bromley, because you would expect a shared room, further away from the City of London to be cheaper. At present, however, you cannot compare prices, or outliers, between categories. Time for a Z-score.
What is a Z-Score?
A Z-score represents the number of standardisations a value is away from the mean. It is used as a method of standardising data, which allows comparison between categories that differ. The calculation that creates a Z-score is as follows...
Based on the boxplot sheet, creating a Z-score does the following...
The individual distributions of price based on room type, now exist as one distribution, and can be described as the number of distributions away from the mean (zero).
What does a Z-score look like in Tableau?
For the purposes of my Z-score, the calculation applies to the 'average price' of an Airbnb.
You don't need to work out the average and standard deviation by hand, a window calc will do it for you.
The Effect
The Z-scores put Airbnb prices on a level playing field. If you look at the Bromley shared room, whilst it is still expensive, at 3.3 standardisations away from the mean, the City of London private room is another standard deviation away from the mean. This shows that, relatively, it is more expensive than the Bromley shared room (rather than by absolute cost which isn't comparable across room types).