percentile.inc/exc please help

[Gewoodruff]

Hello,

I am using percentile.inc/exc to get salary quateriles for the following categories:

Q1: less than 30% 

Q2: 30-45%

Q3: 45%-55%

Q4: 55%-70%

Q5: 70%+

This is based on a column called "Base Pay". This column contains everyones salaries.

I am not sure if im using the right dax by using  percentile.inc/exc but im using it in the following way:

q2 lower = PERCENTILE.EXC([Base Pay],0.3) 

with this column I am trying to achieve what 30% of base pay is. 

i have repeated this to achieve the other % too. 

For some reason i am not getting what 30% of base pay would be (Q5 below is the max of base pay)

q2 lower	q2 max	q4 lower	q4/5	q5 max
27295.33	39367.71	42648.99	60927.07	6383337530

i feel like i might be misunderstanding something?

Any suggestions/ comments about where i might be going wrong?

Kind regards,

Georgia

[v-stephen-msft]

Hi @Gewoodruff ,

Sorry to disturb you...

But did I answer your question ? Please mark my reply as solution. Thank you very much.

Best Regards,
Stephen Tao

[v-stephen-msft]

Hi @Gewoodruff ,

There're some remarks you can refer to

• If column is empty, BLANK() is returned.

• If k is zero or blank, percentile rank of 1/(n+1) returns the smallest value. If zero, it is out of range and an error is returned.

• If k is nonnumeric or outside the range 0 to 1, an error is returned.

• If k is not a multiple of 1/(n + 1), PERCENTILE.INC will interpolate to determine the value at the k-th percentile.

• PERCENTILE.INC will interpolate when the value for the specified percentile is between two values in the array. If it cannot interpolate for the k percentile specified, an error is returned.

• This function is not supported for use in DirectQuery mode when used in calculated columns or row-level security (RLS) rules.

Reference: PERCENTILE.INC

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.