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.

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.

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.

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:

Translate »

Scroll Up
//]]>