Calculating MEDIAN in PivotTables

Unfortunately there’s no built in way to calculate the median in a PivotTable. The approach is different depending on whether you’re using a regular PivotTable or a Power Pivot PivotTable. We’ll look at both options in this tutorial.

The Data

The data we’ll be using in this example is in an Excel Table called Table1:

Download the Workbook

Calculating Median in PivotTables

We’ll look at calculating the Median in a regular PivotTable first. It’s not actually ‘in’ the PivotTable, but rather in a spare column to the right of your PivotTable.

In the image below you can see my PivotTable is in columns B and C, and I’ve put my median formula in column D. I’ve formatted the header in cell D7 with blue fill to make it appear part of the PivotTable, but you if you look in the formula bar you'll see the formula:

The formula is:

=IFERROR(MEDIAN(IF(Table1[Product]=Pivots!B8,Table1[Actual Sales])),"")

It’s an array formula so it’s entered with CTRL+SHIFT+ENTER, which automatically inserts the curly braces you can see surrounding the formula in the formula bar above.

Let’s look at each component of the formula starting with IF:

The IF formula returns an array of values from the Table1 Actual Sales column IF the Product column contains the value in B8 (Atlantic Salmon), of the PivotTable.

In English it reads: if the product is Atlantic Salmon, then return the value from the Actual Sales column.

These values are then passed to MEDIAN which simply returns the median value.

In the event that the formula returns and error, IFERROR will override it and return nothing, as denoted by the "" at the end of the formula:

=IFERROR(MEDIAN(IF(Table1[Product]=Pivots!B8,Table1[Actual Sales])),"")

IFERROR allows me to copy the formula down to row 20, to allow for growth or contraction in the PivotTable that may occur as a result of changes in the Slicer selection, and prevents an error showing in the event of missing data.

Calculating Median in Power Pivot

In Power Pivot we can call on the DAX formula language to write our own median formula that we can use inside a PivotTable (see column I below):

These DAX calculations are known as Measures (in Excel 2013 they are called Calculated Fields). The advantage of using Measures is that we can use the median calculation in the values area just as we would any other item in the field list.

For example, I can easily add the Month field to the columns area to find the median for each product by month:

This flexibility is not available as easily with the formula approach for regular PivotTables that I showed you above. However, with flexibility comes a bit more complexity. Let’s look at how to write the Median formula in Power Pivot's DAX formula language and create the new measure/calculated field.

Inserting a DAX Measure

There are a few places you can insert your measure; in Excel 2013 we’ll use the ‘New Calculated Field’ menu on the Power Pivot tab of the ribbon:

In Excel 2010 it’s called ‘New Measure’:

And in Excel 2016 it’s also ‘New Measure’:

This opens the Calculated Field (measure) dialog box where we:

1. Assign the measure to a table, which is usually the table containing your transactional data. I only have one table in my model, called Table1.
2. Give your measure a name
3. Enter your formula
4. Set your formatting and data type

DAX Median Formula

Ok, let’s address the formula elephant in the room! I know that formula may look a little large, and maybe even daunting.

Fear not. DAX is similar to the Excel formula language, but it has some different functions, and unlike Excel functions, DAX is case sensitive.

Here is the DAX MEDIAN formula (line breaks have been added to help readability):

=(MINX (FILTER ( VALUES ( Table1[Actual Sales] ),
CALCULATE (
COUNT ( Table1[Actual Sales] ), Table1[ActualSales] <= EARLIER ( Table1[Actual Sales] ) )
> COUNT ( Table1[Actual Sales] ) / 2 ), Table1[Actual Sales])
+ MINX ( FILTER ( VALUES ( Table1[Actual Sales] ),
CALCULATE (
COUNT ( Table1[Actual Sales] ), Table1[Actual Sales] <= EARLIER ( Table1[Actual Sales] ))
> ( COUNT ( Table1[Actual Sales] ) - 1 ) / 2 ), Table1[Actual Sales])) / 2

I know it looks scary, but there should be some familiar features, like:

1. It looks similar to an Excel formula; after all, it starts with an equals sign 😉
2. There are parentheses surrounding each function and commas separating arguments
3. It uses the same type of operators, like >, =,-, /
4. It uses Structured References just like an Excel Table e.g. Table1[Actual Sales] references the Actual Sales column of Table1.
5. It even has the COUNT function, just like in Excel!
6. It only references one column; the Actual Sales column, so it’s easy to copy and paste it into your own model and simply edit the Table_Name[Column_Name] components in red to suit.

See, easy! 😉

Notes:

• This formula may be slow on very large data sets.
• In Excel 2016 there is a new MEDIANX DAX function, which means we can replace the elephant sized formula above with this much simpler and more efficient formula.

  =MEDIANX( Table1, Table1[Actual Sales])

Measures Obey Context

You might be wondering how the DAX median formula calculates a different value for each cell in the PivotTable when it only references one column in the formula (the Actual Sales column).

This is because most DAX functions recognise the context of the column and row labels for each cell and automatically apply those filters to the formula. For example; in cell I8 of the PivotTable below the DAX median function is only calculating the median for Brie:

Another way to think of context is similar to an IF function e.g. the median in cell I8 above is much like the MEDIAN(IF… formula I used in the very first PivotTable, except with DAX I didn’t have to write the IF part. The measure did that all by itself based on each value cell’s location in the PivotTable.

Similarly, in cell I11 of the PivotTable below the DAX median function is only calculating the median for Actual Sales IF the Product is Cheddar and the date is 1/02/2015 (d/mm/yyyy):

This flexibility in DAX measures is one of the reasons they’re so powerful. We can build them once and use them again and again.

And since they only calculate on demand, they don’t bog down our file with memory intensive calculations.

Get and Learn Power Pivot

Power Pivot is available as a free add-in in Excel 2010, or Excel 2013 Office Professional Plus, Office 365 Professional Plus, or in the standalone edition of Excel 2013. Note: it is built into Excel 2016 Professional Plus and the standalone Excel 2016 so you don't need to install an add-in.

Power Pivot is not available in Excel for Mac.

If you’d like to learn Power Pivot and DAX formulas, please take a moment to check out my Power Pivot course.

Thanks

Thanks to Marco Russo and Alberto Ferrari at DAXpatterns.com for a wonderful resource on DAX measures, which is where I got the DAX Median formula.

Please Share

If you liked this or know someone who could use it please click the buttons below to share it with your friends on LinkedIn, Google+, Facebook and Twitter.