Percentile Bins, Group by Year

[Rymatt830]

Hello,

I am not sure how to do this so I would love some advice from the all of the experts in this community. I have a table containing five years of data for approximately 250 organizations, roughly 1,250 total observations. Here's what it looks like:

I have a few other variables in the table; but, I am hoping to calculate the quantiles (or, percentile bins, 1-5) for the variable "RatioNetAssetsUnrestricted" for each year, determine what bin each organization falls based on its RatioNetAssetsUnrestricted for that year, and then return that bin number in a calculated column. Here's what I am trying to achieve:

This seems similar to a previous question, here, but I can't determine how to customize that solution to answer my question. Any help will be much appreciated. Thank you!

[CheenuSing]

@Rymatt830

Please try the following

1. Create a  new table called Band with coulmn names - Position, MinimumValue, MaximumValue

2. The rows of this table will be 

        1, 0, 0.30

        2, 0.31,0.50

        3,0.51,0.70

        4, 0.71, 0.90

        5, 0.91, 1.00

3. In the fact table  define a column Bin

        =CALCULATE (
                              VALUES ( Band[Position] ),
                                   FILTER (
                                   Band,
                                   FactTable[RatioNetAssetsUnrestricted] >= Band[MinimumValue]
                                   &&FactTable[RatioNetAssetsUnrestricted] <= Band[MaximumValue]
                              )
                          ) 

This will work. 

If you have any issues let me know.

If you find the above steps solved your problem, please accept as solution and also give Kudos.

[Anonymous]

I think this will get you pretty close?

http://tinylizard.com/segmentation-power-bi/

[Rymatt830]

Thanks for the suggestion, scottsen. But, it won't work in my situation because I can't rely on static bins. My data is linked to a SQL table, which will change because not all of the 2015 data has been input. There are also changes that occur from time-to-time for data that's already been entered. The solution needs to be dynamic.

[Anonymous · Accepted solution]

How about something like:

Bin= SWITCH(TRUE,
Table1[Ratio] <= PERCENTILE.INC(Table1[Ratio], 0.25), 1,
Table1[Ratio] <= PERCENTILE.INC(Table1[Ratio], 0.50), 2,
Table1[Ratio] <= PERCENTILE.INC(Table1[Ratio], 0.75), 3,
4)

?

[Rymatt830]

This is great! Thanks for the quick and easy solution.