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…