Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Percentile calculations in DAX

By Antonio Abalos Castillo,

As noted by several authors, the percentile measures are not part of the calculations available in DAX. In my opinion, this is a drawback, but on the other hand, making these calculations myself improved my understanding and control over the whole tabular model. 

The starting point for this piece was the article written by Colin Banfield called “Creating Accurate Percentile Measures in DAX”, but after trying an implementation, I found that

  1. There are problems when 2 or more measures have the same value
  2. The calculations do not match the ones produced by the Excel formula PERCENTILE.INC or PERCENTILE.EXL

Point number 2 is specially problematic when you have to defend your calculations against a Business Analyst that has created the specifications using Excel. Then you really need to mimic the same calculations that Excel does.

Therefore, I investigated the formulas internally used by Excel, and they are clearly defined in Wikipedia under the R-7 reference. The calculation steps for the percentile 25 are as follows. In my case, I am not calculating the percentile for all the rows in the table but just the ones that match a filtering condition ([FilterColumn] = [FilterCondition]):

RankAscSparse: this calculation will rank the measures in scope in the ascending direction skipping places with ties. For example, if 2 measures have the same lowest value, the third measure will get ranking number 4.

= 
RANKX ( 
    FILTER ( 'Measures', [FilterColumn] = [FilterCondition] ), 
    [Measures], 
    , 
    TRUE (), 
    SKIP 
) 

h25: will estimate what is the measure number of the estimated 25 percentile. For 100 measures, it will be measure 26th.

=
CALCULATE ( 
    ( COUNTROWS ( 'Measures' ) - 1 ) * 0.25 + 1, 
    FILTER ( 'Measures', [FilterColumn] = [FilterCondition] ) 
) 

x25down: will get the value of the measure in 25th position when rounding h25 down.

=
MAXX ( 
    FILTER ( 
        'Measures', 
        [FilterColumn] = [FilterCondition] 
            && [RankAscSparse] <= ROUNDDOWN ( [h25], 0 ) 
    ), 
    [Measures] 
) 

x25up: will get the value of the next measure after the 25th position.

=
MAXX (
    FILTER ( 
        'Measures', 
        [FilterColumn] = [FilterCondition] 
            && [RankAscSparse] <= ROUNDDOWN ( [h25], 0 ) + 1 
    ), 
    [Measures] 
) 

The Percentile 25 calculation will apply linear interpolation to the x25down and x25up items.

=
IF ( 
    [FilterColumn] = [FilterCondition], 
    [x25down] 
        + ( [h25] - ROUNDDOWN ( [h25], 0 ) ) * ( [x25up] - [x25down] ), 
    BLANK () 
) 

With these calculations, I could get the same results as Excel with the PERCENTILE.INC function, and therefore, it was easy to validate the numbers.

Thanks for reading.

Total article views: 2610 | Views in the last 30 days: 27
 
Related Articles
FORUM

Calculation using a Calculated Measure

Calculation using a Calculated Measure

FORUM

Percentile calculations in DAX

Comments posted to this topic are about the item [B]Percentile calculations in DAX[/B] Hi, Thanks...

FORUM

Percentage (calculated measure)

Help on calculated measure

FORUM

Can I find percentile based on dataset data?

Can SSRS calculate a percentile?

FORUM

Calculated Measure

Help on how to code a calculated measure with certain requirements

Tags
dax    
excel    
percentile.inc    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones