Is AVERAGE the right DAX function for Average?
Introduction
The most used functions in DAX have to be SUM(), COUNT() (and it’s colleagues) and AVERAGE(). There is no big deal with the first two. AVERAGE() however, has to be properly thought through before making use of it.
In statistics, we were taught different types of averages. Mean, Median and Mode easily come to mind.
But for some reason, in DAX, AVERAGE() function, which is just Mean is what most people use all the time to compute averges. Whereas, MEDIAN() is also available in the functions library.
Why then, is the AVERAGE() function more popularly used than the MEDIAN() function?
Must we use AVERAGE() function every time we need to compute Averages?
This is another one of those little things we need to be mindful of.
Reminder, difference between AVERAGE and MEDIAN
As mentioned earlier, AVERAGE() function in DAX is simply the Mean of a list of numbers. Mostly used to determine the average value of a column. This average is derived by summing up the values in the list and then dividing the result by the total number of values in the list.
So, for example, a column with these five numbers: 3, 7, 80, 8, 2 will have an average of 20. That is, the sum of 3,7,80,8 and 2 equal 100. That divided by 5 equal 20.
MEDIAN() on the other hand starts by ordering the numbers in ascending or descending order, then uses the mid number as the average.
When there is no single mid number due to having even number of items, the two mid numbers are added and divided by 2 to get average.
Using the same five numbers above (3, 7, 80, 8, 2), MEDIAN() will be computed by sorting the numbers first into 2, 3, 7, 8, 80. Then picks 7 as the average, being the number at the mid point.
When to use MEDIAN()
As we have seen from the earlier illustration, Average for the same set of numbers provided us with two different results. And the two results are even far apart. AVERAGE() gave us 20 while MEDIAN() gave us 7. If I may ask, which of the two is more meaningful for an average in this scenario?
I wouldn’t want to answer that directly. So let’s start by restating the main reason for looking for average. Average is typically used to get the possible center of numbers. Like, Average days for delivery, Average age of students etc.
If the numbers 2, 3, 7, 8 and 80 happen to be number of days it took to deliver a product to 5 different customers, does 20 represent a good average number of days or does 7 make more sense in this case?
You get the idea. When the list of numbers have an outlier, like the number 80 in our case, Average will be skewed because we first add all numbers and divide by number of occurrence. Imagine if one of the numbers here is like 500. Our average number of days would have been over 100 which does not make sense.
Therefore, consider the possibility of having outliers in your column before choosing AVERAGE() over MEDIAN().
When the values in your column have a normal distribution, not too far apart from one another, you can use AVERAGE().
In such case, even if you use MEDIAN(), the value will not be far off from the value you’d get when you use AVERAGE.
On the other hand, if the column in question does not have a normal distribution or contains outliers. Where some numbers are way far off from some other numbers, MEDIAN() should be your go to function to calculate Average.
Conclusion
In conclusion, choose your Average function carefully by considering the range of values you have in the data column. It seems also that MEDIAN() can be a safer bet. Or what do you think?
Read about MEDIAN from DAX Documentation.
Ps: share these free intro courses with anyone you think can benefit:
