PowerPivot percentiles


New Member
Jan 7, 2013

I've looked at a number of different posts from bloggers about finding a workaround for the percentile feature in DAX.

I am trying to calculate a dynamic percentile measure that correctly calculates the right Xth percentile of price for a given year and/or month. For example, if I have 2007, 2008, 2009, etc. in my pivot table, then I would like a percentile formula to show me the correct 5th, 10th, 15th, etc. percentile for that year. Ditto if I add month (although right now, I would be happy to get the year part working!)

Using combinations of a couple of posts on this topic, I have managed to create measures that work for the entire data set. However, they do not work for subsets. For example, my 5th percentile measure correctly calculates the 5th percentile of the entire price column of my table, but unlike other measures it does not adjust to context when I pivot this measure against, say, YEAR. The grand total will show the correct 5th percentile but the result for each individual year will be meaningless.

I am new to PowerPivot and am at a loss about how to do this. Any assistance would be greatly appreciated!

Table: My data table (ZE_AB_pool_price) has several columns: OPR_DATE, OPR_HOUR, PRICE, PRICE_BIN, LOOKUP_HOUR, LOOKUP_DATE, VOLATILITY, LN_HOURLY, MONTH, and YEAR. Some of these are calculated and others are imported directly from an Oracle database. Each row in the table has a unique OPR_DATE and OPR_HOUR pair (i.e. there is one record for every hour).

Current measures (5th percentile):
Step 1: Calculate the rank of all items in my table:
rank:=rankx( ALL(ZE_AB_pool_price), [sum_price],,1)

Step 2: Calculate the rank corresponding to the 5th percentile:
Rank2_5:=calculate ( (countrows (ZE_AB_pool_price)-1)*5/100+1, all(ZE_AB_pool_price))

Step 3: Calculate the data value corresponding to the rank in Step 2. This is the upper percentile.
Up_5:=maxx (filter (ZE_AB_pool_price, [rank]<= roundup ((count([PRICE])-1)*5/100+1,0)),[PRICE])

Step 4: Caculate the data value corresponding to the rank in Step 2. This is the lower percentile.
Down_5:=maxx( filter (ZE_AB_pool_price, [rank]<= int((count([PRICE])-1)*5/100+1)),[PRICE])

Step 5: Perform a linear interpolation to find the correct 5th percentile value.
Percentile_5_2:=[Down_5]+ ([Up_5]-[Down_5])* ([Rank2_5]-rounddown([Rank2_5],0))

I used these two posts as sources. It appears the second post has an example of what I want to do but I couldn't figure out how to adapt it to my table structure.

Creating Accurate Percentile Measures in DAX – Part I « PowerPivotPro
Creating Accurate Percentile Measures in DAX – Part II « PowerPivotPro

Statastizard: PowerPivot Percentile DAX Formula

Some videos you may like

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)


New Member
Nov 12, 2019
Using PowerPivot / DAX, I think you should be able to return the percentiles in your pivot table using these steps. In this example I want to rank "ID" by whatever values I've selected in the pivot table.


Step 1:

Create a rank measure, e.g.,:

Rank:=IF(NOT(ISBLANK([Average Value])),RANKX(ALLSELECTED(Facts[ID]),[Average Value],,DESC,Dense),BLANK())

Step 2:

Create a measure to count the unique entries included in that ranking set, e.g.:


Step 3:

Create a percentile measure:

Percentile:=[Rank]/[Unique Values in Selection]


These two links explain the above steps in more detail.

#PowerPivot #DAX #Percentiles #Rank #Measures

Watch MrExcel Video

Forum statistics

Latest member

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...