Percentile in PowerPivot DAX

	When analyzing data, I often find myself computing the percentile value. As I am a big fan of PowerPivot, I went with it lately to analyze some data and tried to get the 90th percentile of my series. So I chose to create a DAX measure and went looking for the PERCENTILE function only to discover that:
Normal Distribution courtesy of Peter Strandmark @ Wikipedia

PowerPivot DAX doesn't support PERCENTILE! Even more shocking, neither is the much more common MEDIAN measure supported.

Don't panic folks; Marco Russo was kind enough to share his way of computing the median. With a tiny tweak it allows us to compute the 90^th percentile as such:

MINX(FILTER(VALUES(MyDataSerie1[Col1]), CALCULATE(COUNTROWS(MyDataSerie1), MyDataSerie1[Col1] <= EARLIER(MyDataSerie1[Col1]) ) > COUNTROWS(MyDataSerie1)*0.9), MyDataSerie1[Col1])

The important part is 0.9. Just replace it with 0.5 for the median, 0.25 for the first quartile and so forth.

I bet the Excel and SQL folks had good reasons not to include these functions, if you have any idea why, leaves me a message. I also posted a line on the MSDN forum(http://social.msdn.microsoft.com/Forums/en/sqlkjpowerpivotforexcel/thread/b1ccf0e6-d5d3-4405-9748-4381a310b57b ).

Accuracy of this measure

I am no statistician, the formula looked ok but I wanted more assurance about its accuracy. Thus I fixed a little test to compare the percentile computed with this DAX measure to regular Excel PERCENTILE.INC and PERCENTILE.EXC formula. I computed the 5,25,50,75,90,95 percentiles using these 3 means for 3 different kind of number series and then averaged the difference between those :

DiffINC is the difference in percentage between PERCENTILE.INC and the DAX measure. Same for DiffEXC.

As you can see, this DAX measure is giving results pretty close (less than 0,05% error) to the PERCENTILE.INC Excel formula. This is close enough for my needs anyway. Yet be careful, with smaller number series (less than 50 items), the error went up to reach values as high as 5% on some cases.

Percentile Explanation

By the way, for those unfamiliar with percentile, the Nth percentile is the value below which N% of the value fall. More details on Wikipedia.

Ok, so why it is useful you might ask, well it is common to analyze your data series without the extreme values that might indicate problem with your data source. An example is in the software performance testing field where you will usually consider the 90^th or 95^th values of the response time in order to know what the reasonable highest response time is without considering test aberrations or errors.

N.B: Some of you might have noticed the new addition to the title of this blog: Jonathan's IT blog – SharingPointers, tell me what you think in the comments !

PowerPivot - Choosing a calculated column type

Lately I have been using PowerPivot (more details on this at the end of this post) and I came across a problem whose solution might interest others.

The Problem

As I created a calculated column based on a date column, the result was always displayed as a number instead of a date. Worst the drop down list where you can choose the type of the column was greyed out.

Here is an example; CalculatedColumn1 is created using some number function (ROUNDDOWN). Yet I am working with Dates, while PowerPivot understandably consider the results to be a number, one can’t override this automatic setting of the Data Type. The “Data Type” DropDown list is just greyed out with a value of Number (Nombre in French in the screenshot below) :

The obvious drawback is that my column isn’t very readable, I mean no normal human beings can understand that 40255 mean 18/03/2010 00:00, else leave me a comment, I would be interested to meet you :)

My solution

Here is my workaround, just throw a date function in your function. In my case I choose to add TIME(0,0,0) which is a neutral operation :

Thus PowerPivot will understand that your column is a date, and you will be able to select a Date format:

Some Information on PowerPivot (aka Gemini)

For those who don’t know what PowerPivot is, you might know by its codename, Gemini. It is a Microsoft Addin for Excel and SharePoint 2010 that allows you to analyze tons of data very easily. It’s supposedly really fast due to its in In Memory Analysis. For BI noobs (a group I am sadly part of) you might see it as a personal version of Anaysis Service (PowerPivot for Excel) or as a Team flavored BI (PowerPivot for SharePoint), on this subject I found this blog post by the PowerPivot Team very interesting (http://blogs.msdn.com/powerpivot/archive/2010/03/12/comparing-analysis-services-and-powerpivot.aspx).

Microsoft released a series of nice videos to explain PowerPivot, here is the first one :

Here is what PowerPivot promises and, as far as my experience went, delivers:

• Analysis (Pivot Table and graph) of Data from multiples sources (DataBase, Excel files, flat files)
• Analysis of huge volume Data (I am talking millions here)
• Integration of simple relational constraint between your data sources
• And maybe the coolest part is that this is available to any Information Worker already familiar with Excel. I would take this one with a grain of salt; your Information Worker needs to be quite technical savvy to fully benefit from this tool.

Additionally the Integration with SharePoint is supposed to bring added performance and to deliver your reports more easily to multiple persons. I haven’t tested the integration, yet!

After spending some quality time with PowerPivot I have got to admit being very enthusiastic about this product. I can see numerous scenarii where it would have saved me hours in the past (More details and cool links at the end of the post). I just can’t wait to set my hands on the RTM version of Excel 2010 and PowerPivot to enjoy this tool without worrying so much about the too frequent crashes (in my experience double clicking on a graph horizontal axis freezes quite often).

To go further I would advise you to start with the official PowerPivot site (http://www.powerpivot.com ), especially the demo part.

To my French reader I would also recommend the hilarious BI video by Têtes à Claques that you might have enjoyed at the 2010 Techdays in Paris :(http://www.microsoft.com/france/serveur/sql/secretedouard/ )