# PowerPivot percentiles

#### Viceroy369

##### New Member
Hello,

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

### Excel Facts

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

#### Louis_Guionneau

##### New Member
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.:

Unique IDs in Selection:=CALCULATE(DISTINCTCOUNT(Facts[ID]),ALLSELECTED(Facts[ID]))

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

#### Louis_Guionneau

##### New Member
Try this - PERCENTILEX.INC(ALLSELECTED(Facts[ID]),SUMX(ALLSELECTED(Facts[Period]),[Sum Values]),[Percentile Definition])

#### Attachments

• 17.5 KB Views: 22

### Forum statistics

1,101,785
Messages
5,482,915
Members
407,368
Latest member
FunkyFriedChicken

### 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...
• CELL FORMAT - IF CONDITION
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...