Segment data based on percentile by group

[Anonymous]

Greetings,

I have a table like this one:

GROUP	VALUE
A	1
B	10
A	2
A	2
A	3
B	12
B	15
A	2
B	18
B	14

I´d like to find percentiles by group so I can filter the data that is too high or too low for each group:

GROUP	VALUE	P20	P80	FILTER
A	1	1	3	0
B	10	10	18	0
A	2	1	3	1
A	2	1	3	1
A	3	1	3	0
B	12	10	18	1
B	15	10	18	1
A	2	1	3	1
B	18	10	18	0
B	14	10	18	1

Segmented data:

GROUP	VALUE
A	2
A	2
B	12
B	15
A	2
B	14

Anyone knows how to find percentiles by group either on DAX or Power Query? I´ve been looking but haven´t found something like this.

Thanks, best regards,

Alejandro.

[lc_finance · Accepted solution]

Hi @Anonymous ,

You can download my proposed solution here.

Here are the DAX formulas for the 3 calculated columns:

1) 20th percentile

Perc 20 = 
VALUE(PERCENTILEX.EXC(
    FILTER('Table' ,[GROUP]=EARLIER('Table'[GROUP]) )
    ,[VALUE] , 0.2))

2) 80th percentile

Perc 80 = 
VALUE(PERCENTILEX.EXC(
    FILTER('Table' ,[GROUP]=EARLIER('Table'[GROUP]) )
    ,[VALUE] , 0.8))

3) Filter

Filter = IF([VALUE]>[Perc 20] && [VALUE]<[Perc 80],1,0)

Finally, here is a screenshot of what it looks like.

Does this help you?

Do not hesitate if you have more questions.

LC

Interest in Power BI and DAX templates? Check out my blog at www.finance-bi.com

[Anonymous]

That works, thanks for the help.

I got this error with the production data though:

The percentile value should be in the range 1/(N+1)..N/(N+1) inclusive, where N is the number of data values

Changing from PERCENTILEX.EXC to PERCENTILEX.INC solved it.