Page 1 of 1

Dynamic Percentile - Year or Month

Posted: Mon Nov 18, 2019 12:55 am
by erock24
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 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


Re: Dynamic Percentile - Year or Month

Posted: Fri Nov 22, 2019 6:49 pm
by Bernard Heymans
You find here below 2 possible solutions:

FIRST: create a measure for each result expected.


Create a table with all the percentils you want to see.
Create a measure (here PC := AVERAGE(percentils[percentils])

on the data table create a measure percentils:= PERCENTIEL.INC(data[Value] , PC)

Show pivot.

Re: Dynamic Percentile - Year or Month

Posted: Sat Nov 23, 2019 4:18 am
by erock24
This is not the right calculation. Not sure what your measures are doing, but the end calculation should tie back exactly to what I show in GREEN in my attachment. Is it even possible to jump from the raw data to the green table? It might not be.

Re: Dynamic Percentile - Year or Month

Posted: Mon Nov 25, 2019 8:58 pm
by Bernard Heymans
The difference in figure seems to be because you make an average per iteration & per year & then afterwards you make an average per year.

My figures are produced making an average par year.

So if a year has 8 figure & the next only has 2 , in your way they are calculated with the same weight... not in mine.

so I addapted my query to create the averages per year table...

BUT: you could do it without any visuals...