Guest post by Scott Senkeresty

Get it?  “Median?”  SO Funny!

### Intro

Rob is on-site with a client this week, so the reins, mic, baton or other appropriate metaphor gets handed to me today.  We get to today’s topic by means of a discussion on calculating a median in DAX:

Scott:  That sounds easy.  Just use TopN to grab half the numbers in ascending order, then another TopN against descending values to grab the final value(s)  (Glossing over odd vs even number of data rows)
Rob: What are you going to do about ties?
Scott:  <Blank Stare>

It turns out that calculating a median in DAX is pretty tricky.  Rob contends that sneaky street fighting tactics are required to deal with ties…where in my heart of hearts, I believe an elegant solution exists.  Hopefully we can get to the bottom of that in a future blog post.

For today, I want to use Excel 2013 to show an easy way to “see” how TopN handles ties.

Let’s start with this clearly important real world data on the awesomeness of various colors, displayed to the right.

I’m doing some iterative development of my measure… and as an intermediate step I just want to make sure TopN is working like I expect (and that I didn’t forget the ALL() surround my table parameter).

Now, there is no direct way to display those rows in Excel.  Maybe as an intermediate debugging step we write the measure wrapped by COUNTROWS to make it legal and admire our answer of “3”.

Here is our measure (which, btw, ignores the case where the same Color shows up more than once I the table)

Top3:=
COUNTROWS(TOPN(3, ALL(ColorTable), ColorTable[Awesomeness]))

And low and behold, our minds are blown!  I mean, we even picked 3 because the first 3 values of Awesomeness were all 1, and ties weren’t even going to be an issue!   How can there be 5 rows returned!?

Now I am left head scratching, and really want to know what rows DAX thinks it is counting!    Good news!  This is easy!  Much easier than implementing a proper median measure.

### Display Rows via Table Queries

Shout-out to Zack Barresse who brought this technique to our attention!

Step 0:  This only works in Excel 2013, not in Excel 2010.  Sorry folks.

Step 1:  On the Data ribbon, select “Existing Connections”.   On the dialog that displays select the Tables tab, and find the Connection to your table and double click that sucker.

Step 2: Leave “Table” selected, place the data where you want, and click OK.

Step 4:  Right Click your new table, select Table > Edit DAX.  Flip the command type to DAX, and get to the good stuff!   You call EVALUATE and the first parameter is a table.  Lucky for us, TOPN returns a table!  So we paste our TOPN in there…  and click OK…

So, we quickly see two things here:

1. TOPN defaulted to descending.   We didn’t get 1,1,1… we got 10,7,2.   The fourth (optional) parameter to TOPN controls ascending/descending.  Guess I should have read the docs.
2. And the reason we saw “5” was that, indeed as Rob said, ties are interesting.  All ties get returned.  The docs might mention that too

### Wrap Up

So there you have it.  If you are using Excel 2013, you have an interesting debugging technique at your disposal.  Hope you find that useful.

And for those wondering about that Median problem…imagine you have { 1, 2, 4, 4 }.  If TOPN did not return duplicates, you would just take the TOP3 ascending (1,2,4), then the TOP2 descending (2,4), AVERAGE those together (3) and dance.

Instead, that first TOP3 returns… all 4.  Then dogs and cats are living together… and you are totally not dancing.  A discussion for another day.

X

## This Post Has 7 Comments

1. Andrew Todd says:

Hi Scott –

Thank you for the informative post! I have developed what I believe to be an elegant solution to calculation median in PowerPivot and would love to share it with you. Is there a contact email that I could reach you at for further collaboration?

Thank you,
Andrew

2. David Churchward says:

Brilliant Scott and great to read. Looking forward to seeing more.

Cheers
David

3. Very cool! Could this same technique be done in 2010 using Power Query?

4. Hi , what do i need to have table option active in Import Data window?Thank you for help

5. tim says:

I have MS Professional Plus 2013. When I select Table > Edit DAX, I see 5 new options, but 4 are grayed out. Only ‘Unlink from Data Source’ is active. 🙁

1. John M says:

The only way I could recreate the error you stated using Excel 2016 was by deleting the sheet from PowerPivot after creating the table in Excel by clicking on one of the Existing Connections. I suggest that you create your own VERY SIMPLE table in a new spreadsheet, link that table into PowerPivot, and pull that table back into excel using Existing Connections.
For your first attempt to use ‘Edit Dax’, I would simply write the word Evaluate followed by the name of your table. For the example above, I would write

Evaluate ColorTable

I would try more advanced queries only after establishing the connection using Evaluate followed by the table name. Once you understand how to query your data model using Evaluate, you will start to truly understand how Power Pivot works.

Don’t give up. It’s worth the effort.

Good Luck!!