PowerPivot percentiles

[Viceroy369]

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

 

[Louis_Guionneau]

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.

Creating Accurate Percentile Measures in DAX – Part I

Guest Post By Colin Banfield 29th September, 2011 Equivalents of Excel's Percentile, Quartile, and Median functions are perhaps the most significant

powerpivotpro.com

Percentile as a measure

Hi, I calculated the ranking of a data set using this DAX expression: Rank = if(not(ISBLANK([Avg Value1])), RANKX(ALLSELECTED('Table1'[ProductID]), [Avg Value1], , DESC, Dense), Blank()) which returns the ranking of all products by average Measure1, now my problem is that the ranking is a...

community.powerbi.com

#PowerPivot #DAX #Percentiles #Rank #Measures

 

[Louis_Guionneau]

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