Skip to main content
cancel
Showing results for 
Search instead 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

Reply
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 #StatusInfo Received DateComplete Info Received DateDifference
1Cancelled2020-10-282020-10-280
2Complete2020-01-272020-02-1010
3In Progress2020-07-142020-07-140
5Planned2020-09-292020-10-2619
6On Hold2020-02-032020-03-0220
7Cancelled2020-02-132020-03-1220
8Complete2020-02-232020-03-2219
9In Progress2020-03-042020-04-0120
10Executed2020-03-142020-04-1119
11Planned2020-03-242020-04-2120
12On Hold2020-04-032020-05-0120
13Cancelled2020-04-132020-05-1120
14Complete2020-04-232020-05-2120
15In Progress2020-05-032020-05-3119
16Executed2020-05-132020-06-1020
17Planned2020-05-232020-06-2019
18On Hold2020-06-022020-06-3020
19Cancelled2020-06-122020-07-1020
20Complete2020-06-222020-07-2020

 

Appreciate your help and support in advance.

 

Regards,

Salman

2 ACCEPTED SOLUTIONS
VahidDM
Super User
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:

VahidDM_1-1629260344006.png

 

 

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

Appreciate your Kudos VahidDM_0-1629260278584.png !!

 

View solution in original post

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

vxiaotang_0-1629451743359.png

 

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.

View solution in original post

5 REPLIES 5
v-xiaotang
Community Support
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

vxiaotang_0-1629451743359.png

 

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!

VahidDM
Super User
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:

VahidDM_1-1629260344006.png

 

 

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

Appreciate your Kudos VahidDM_0-1629260278584.png !!

 

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 🙂

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 VahidDM_0-1631242953806.png !!

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

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