cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Gewoodruff
Frequent Visitor

percentile.inc/exc please help

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 lowerq2 maxq4 lowerq4/5q5 max
27295.3339367.7142648.9960927.076383337530

 

i feel like i might be misunderstanding something?

 

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

 

Kind regards,

 

Georgia

2 REPLIES 2
v-stephen-msft
Community Support
Community Support

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
Community Support
Community Support

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.

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!