Most of us know what an average is. Simply add all values and devide by the number of values. Its pretty simple until it is not.
Lets look at the following example:
I need 100 apples, 100 pears and 100 oranges. I go to shop one and they have only 70 of each. The price of the apples is £1 per apple, pears are £1.1 per pear and one orange is £2. Now I have 70 of each and I have spend
70x1 (apples) +70x1.1 (pears) +70x£2 (oranges) =
70 (apples) + 77 (pears) + 140 (oranges) =£287
Now I need 30 more of each. I go to shop two. They have what I need but the prices are a bit higher - one apple is £2, one pear is £2.5 and one orange is £3. So I open my wallet and pay
30x2 (apples)+ 30x2.5 (pears) +£3x30 (oranges) =
60 (apples) + 75 (pears) + 90 (oranges) = 225.
The question is: What is the average price per fruit? If we calculate it by using the easy average formula we will get to this values:
(£1+£1.1+£2+£2+£2.5+£3)/6 = £1.9333 per fruit
A better quesitons is: What is the weighted average price per fruit?
[30x2 (apples)+ 30x2.5 (pears) +£3x30 (oranges) + 30x2 (apples)+ 30x2.5 (pears) +£3x30 (oranges)]/300 = 512/300 = £1.70667 per fruit.
The two average prices are different because I bought a lot more fruit at the lower price than the quantity of fruit bought at the higher price. For this reason using the weighted average calculation is giving us more accurate value.
We can very easily do this in tableau by creating only one simple calculated fields
This is an example of the calculated field used to calculate the average price per property type for the AirBnB properties weighted at the number of reviews.
And here is the result:
Voila!