Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.