Power Pivot Principles: Introducing the MEDIAN Function

Welcome back to our Power Pivot blog. Today, we discuss how to use the MEDIAN function in Power Pivot.

Before we get into what the MEDIAN function does, let’s answer the question: what does median mean?

The median is the middle value in a sorted list of numbers. For example, if we have the following set of values:

6, 1, 2, 9, 3

We have to arrange these values in ascending order:

1, 2, 3, 6, 9

Then we can then determine that the median is 3.

If we have an even number of values, e.g.

4, 5, 8, 10

We would take the average of the middle two numbers ((5 + 8) / 2). Therefore, the median would be 6.5 in this case.

OK, so what does the MEDIAN function do?

The MEDIAN function returns the median value from a column of values in a data table. The MEDIAN function ignores blanks when calculating the median value in a column and returns with a decimal number as a result.

Let’s take a look at an example. We will create a simple measure using the MEDIAN function using the data from this Table:

After adding this table to our Data Model, we can create the following measure:

Exporting this measure to a PivotTable, we get:

Power Pivot has automatically arranged our dataset in ascending order and calculated the Median value. Neat! This means that the MEDIAN function in Power Pivot will not be affected by the way we order the values in our columns and will calculate the median independently.

Let’s try adding some blank cells into our data set:

Refreshing our PivotTable yields… the same value:

We can see that the MEDIAN function ignores blank cells in our data set, similar to its equivalent function in Excel.

That’s it for this week, tune in next week for more Power Pivot! Until then happy pivoting!

Stay tuned for our next post on Power Pivot in the Blog section. In the meantime, please remember we have training in Power Pivot which you can find out more about here. If you wish to catch up on past articles in the meantime, you can find all of our Past Power Pivot blogs here.