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 3: Briefly admire your basically un-interesting table that got inserted:
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…
Step 5: Admire the updated table. Shake head.
So, we quickly see two things here:
- 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.
- 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 Adaptive ascending (1,2,4), then the TOP2 descending (2,4), AVERAGE those together (3) and dance.
Instead, that first TOP3 Adaptive returns… all 4. Then dogs and cats are living together… and you are totally not dancing. A discussion for another day.
