cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

## Percentile calculation based on other column in the data

Hi All,

I am trying to calculate percentile value of one column, based on conditions of another column. Bleow is my sample data set; I need to calculate 95th percentile of the column difference, but for only those values where "Status" is not Cancelled or On hold! I am using below:

P95 = PERCENTILE.INC('Sheet1'[Difference], 0.95)

How do I modify this to acheive result I am looking for!

 Account # Status Info Received Date Complete Info Received Date Difference 1 Cancelled 2020-10-28 2020-10-28 0 2 Complete 2020-01-27 2020-02-10 10 3 In Progress 2020-07-14 2020-07-14 0 5 Planned 2020-09-29 2020-10-26 19 6 On Hold 2020-02-03 2020-03-02 20 7 Cancelled 2020-02-13 2020-03-12 20 8 Complete 2020-02-23 2020-03-22 19 9 In Progress 2020-03-04 2020-04-01 20 10 Executed 2020-03-14 2020-04-11 19 11 Planned 2020-03-24 2020-04-21 20 12 On Hold 2020-04-03 2020-05-01 20 13 Cancelled 2020-04-13 2020-05-11 20 14 Complete 2020-04-23 2020-05-21 20 15 In Progress 2020-05-03 2020-05-31 19 16 Executed 2020-05-13 2020-06-10 20 17 Planned 2020-05-23 2020-06-20 19 18 On Hold 2020-06-02 2020-06-30 20 19 Cancelled 2020-06-12 2020-07-10 20 20 Complete 2020-06-22 2020-07-20 20

Appreciate your help and support in advance.

Regards,

Salman

2 ACCEPTED SOLUTIONS
Super User

Hi @Anonymous

Try this measure:

``````P95 =
CALCULATE (
PERCENTILE.INC ( 'Table'[Difference], 0.95 ),
FILTER (
'Table',
'Table'[Status] <> "Cancelled"
&& 'Table'[Status] <> "On Hold"
)
)``````

the Output will be as below:

Did I answer your question? Mark my post as a solution!

Appreciate your Kudos  !!

Community Support

Hi @Anonymous

you can try this

``P95 = IF(MIN(Sheet1[Status])<>"Cancelled"&&MIN(Sheet1[Status])<>"On Hold",PERCENTILE.INC('Sheet1'[Difference], 0.95),BLANK())``

result

Best Regards,

Community Support Team _Tang

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

5 REPLIES 5
Community Support

Hi @Anonymous

you can try this

``P95 = IF(MIN(Sheet1[Status])<>"Cancelled"&&MIN(Sheet1[Status])<>"On Hold",PERCENTILE.INC('Sheet1'[Difference], 0.95),BLANK())``

result

Best Regards,

Community Support Team _Tang

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

Anonymous
Not applicable

@v-xiaotang Thank you for the support!

Super User

Hi @Anonymous

Try this measure:

``````P95 =
CALCULATE (
PERCENTILE.INC ( 'Table'[Difference], 0.95 ),
FILTER (
'Table',
'Table'[Status] <> "Cancelled"
&& 'Table'[Status] <> "On Hold"
)
)``````

the Output will be as below:

Did I answer your question? Mark my post as a solution!

Appreciate your Kudos  !!

Anonymous
Not applicable

@VahidDM thank you for the help. However, I a question - why did you use "&" in filter? shouldnt it be "or(||)"?

P.S. I am very new to Power BI 🙂

Super User

Hi @Anonymous

I used && as an AND to set a filter as you mentioned; you can use || as an OR if you want to change the filter.

https://docs.microsoft.com/en-us/dax/or-function-dax

https://docs.microsoft.com/en-us/dax/and-function-dax

Appreciate your Kudos  !!