Dynamic Percentile - Year or Month

Anything related to PowerPivot and DAX Formuale
erock24
Posts: 2
Joined: Sun Nov 03, 2019 9:37 am

Dynamic Percentile - Year or Month

Postby erock24 » 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
Attachments
Example_Avg.xlsx
Avg by year
(453.91 KiB) Downloaded 21 times

Bernard Heymans
Posts: 28
Joined: Wed Feb 28, 2018 12:18 am

Re: Dynamic Percentile - Year or Month

Postby Bernard Heymans » Fri Nov 22, 2019 6:49 pm

You find here below 2 possible solutions:

FIRST: create a measure for each result expected.


SECOND:

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.
Attachments
Example_Avg.xlsx
(560.29 KiB) Downloaded 5 times

erock24
Posts: 2
Joined: Sun Nov 03, 2019 9:37 am

Re: Dynamic Percentile - Year or Month

Postby erock24 » Sat Nov 23, 2019 4:18 am

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.

Bernard Heymans
Posts: 28
Joined: Wed Feb 28, 2018 12:18 am

Re: Dynamic Percentile - Year or Month

Postby Bernard Heymans » Mon Nov 25, 2019 8:58 pm

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...
Attachments
Example_Avg (1).xlsx
(771.52 KiB) Downloaded 11 times


Return to “PowerPivot/DAX”

Who is online

Users browsing this forum: No registered users and 0 guests

cron