Percentile calculation based on other column in the data

[Anonymous]

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

[VahidDM · Accepted solution]

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  !!

[v-xiaotang · Accepted solution]

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]

@v-xiaotang Thank you for the support!

[VahidDM · Accepted solution]

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]

@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 🙂

[VahidDM]

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  !!