Tableau’s Analytics pane allows you to slap an average line onto your view. Drag, drop, done?
Not quite. In this blog, I will show you an example of when the average line Tableau creates for you may not be doing what you think it does.
So Figure 1 below shows a vertical bar chart with the height of each bar indicating the number of customers that fall into each number of orders bucket. For example, 134 customers have ordered 5 times; 1 customer has ordered 17 times.
Average Method 1
Now, what if you want to see how many orders your customers have on average? Let’s drag an average line from the Analytics pane onto the view as shown in Figure 2:
What you get, is an average line showing 7.71 (see Figure 3).
Now is this correct? Yes and no. Figure 4 shows how Tableau is calculating your average. Because we created our average line on Orders Per Customer (the x-axis), Tableau is adding up all the values on the x-axis and dividing it by the count of values. What you may have wanted (and what is probably better) is a weighted average, one that takes into account the number of customers in each bucket instead of treating each bucket as equal value.
Average Method 2
Orders Per Customer, on the Columns shelf, is an LOD as shown in Figure 5. It counts the number of orders for each customer.
Now let’s write another calculation. In this one, we would like to calculate the weighted average Orders Per Customer, i.e. the total number of orders divided by the total number of customers.
The result of this average calculation is 6.317, as shown in Figure 7.
So why is this different? Figure 7 shows how this LOD is calculating this average:
Which one is right?
Both are technically correct but Method 2 is probably less confusing and more what you would expect from an average. To prove why Method 2 is better, take a look at what happens when I select two Orders Per Customer values, 5 and 6:
The average is being recalculated as 5.5 for these two values, i.e. the mid-point between 5 and 6. 5.5 would only be the correct answer if there were the same number of customers for each bucket. But since 134 customers have ordered 5 times and 107 have ordered 6 times, the weighted average is 5.44.
Be careful next time you add an average line. Make sure it’s doing what you want it to do!
That’s all, folks!