### Dynamic Percentile - Year or Month

Posted:

**Mon Nov 18, 2019 12:55 am**I have iteration data: Iteration, Year, Month, Type, Value. Can ignore the Type field, it's the same for each iteration. Iteration field contains 201 entries, with iterations 1-200 I am looking for a measure that will calculate percentile. I need to calc Max, Min, P95, P5, and P50. In excel if I use the percentile.inc function with k = 1, 0, .95, .05, and .5 I get exactly the answer I want. However, I have to apply that function to a pivot table that has iterations as rows (not including iteration 701, only 1-200), and time period as columns. This could be year and month or just year and then the value gets summed or sometimes avg. Is there a way in PowerPivot to create these measures so that I can build a pivot with just time period and pull in correctly calculated percentiles without having to pull back the underlying iterations and use the excel function? Maybe have a percentile table in PP so I can add additional ones if need be like P75/P25?

I attached a file with an example. In green would be what I would ultimately like to simply pull from PowerPivot without having to construct the blue pivot table and apply the excel functions. Columns A:E is the raw data that would be stored in a PowerPivot table. Notice how my pivot table in this case Averages by year, some table I would want to sum instead. Also notice how I ignore iteration 701, that's a reference case and not tied to the stochastic iterations 1-200

Any help is very much appreciated. Thanks for your time

-Eric

I attached a file with an example. In green would be what I would ultimately like to simply pull from PowerPivot without having to construct the blue pivot table and apply the excel functions. Columns A:E is the raw data that would be stored in a PowerPivot table. Notice how my pivot table in this case Averages by year, some table I would want to sum instead. Also notice how I ignore iteration 701, that's a reference case and not tied to the stochastic iterations 1-200

Any help is very much appreciated. Thanks for your time

-Eric