Forum Discussion

Saranya3_d's avatar
Saranya3_d
Helper I
6 years ago

Percentile

<top> PercentileHi Experts, I’m looking for percentile calculation in powerbi report. The In-build formula is not working out for my requirement, so I have written an query to fetch the outcome as per the requirement.

 

Screenshot 1: - Priority details along with the overall MTTR (time taken to resolve the ticket) 

 

Screenshot 2:- Here is my question. 

Deatils of the table which is available in screenshot 2. 

Priority – Classified the priority numbers

Total no of ticket count - Split-up of individual priority count

P95 count - excel calculation -  = (total no of ticket count/100)*95) & Power Bi calculation = (CALCULATE(COUNT('Table'[Incident Id]),'Table'[priority]) / 100)*95

P95 = I have to pull the corresponding Priority MTTR which is available in P95 cell from the screenshot 1.

For ex: p95 count is showing as “4” then I have to go to screenshot 1 and do the sort from ascending to descending order and then will look for 4th ticket overall MTTR and will place the same in the corresponding cell. Will follow the same for rest of the cells too in the table.

 

I have taken percentile count for p95, p75 & p50. Now looking for the assistance to arrive the corresponding MTTR for the cells.  Can any one please help me?

 

Thanks,

Saranya

11 Replies

  • MFelix's avatar
    MFelix
    Super User
    6 years ago

    Hi Saranya3_d ,

     

    You need to create a ranking in order to compare with the percentile value and then use that to find your MTTR.

     

    • Create an ID column on the MTTR table that will be used for your ranking (just add zeros to the rigth in order to have all the hours with the same length:

     

    Priority MTTR ID
    P1 3:46:38 003:46:38
    P1 5:49:01 005:49:01
    P2 10:00:01 010:00:01
    P1 32:08:44 032:08:44
    P1 93:58:18 093:58:18
    P2 50:00:50 050:00:50
    P2 413:00:17 413:00:17

     

    • Depending on the way you want to do things you may want to do the ranking wiht a measure or with a calculated column result is similar however the measure allows to be more dinamic
    Measure  Ranking =
RANKX (
    FILTER (
        ALLSELECTED ( MTTR_Table ),
        MTTR_Table[Priority] = MAX ( MTTR_Table[Priority] )
    ),
    CALCULATE ( MAX ( MTTR_Table[ID] ) ),
    ,
    asc,
    DENSE
)

Column Ranking =
RANKX (
    FILTER ( MTTR_Table, MTTR_Table[Priority] = EARLIER ( MTTR_Table[Priority] ) ),
    MTTR_Table[ID],
    ,
    asc,
    DENSE
)

     

    Now just add the following measure for each Percentile:

    P95 MTTR =
CALCULATE (
    MAX ( MTTR_Table[MTTR] ),
    FILTER (
        ALL ( MTTR_Table ),
        [Measure  Ranking] = [P95]
            && MTTR_Table[Priority] = SELECTEDVALUE ( Priority[Priority] )
    )
)

    Formula is based on the measure but you can do it based on the column. Also I did not know if you had a Priority for dimension and connecting both of the table but this is not

     

    Check result in attach PBIX.

    • Saranya3_d's avatar
      Saranya3_d
      Helper I
      6 years ago

      Hi MFlexi,

       

      Thanks a ton for your response!

       

      I did the same in my pbix file but MTTR is showing as blank. i have attached the screenshot of the pbix file (no measure or columns in the data) for your reference. I tried to attach here but couldn't see the attachment icon. 

       

      Request you to help me to arrive the percentile please. It would be great help :)!!

       

      Waiting for your reply! Thanks in Advance!

       

      Thanks,

      saranya 

  • v-diye-msft's avatar
    v-diye-msft
    Community Support
    6 years ago

    Hi Saranya3_d 

     

    if the above posts help, please kindly mark it as a solution to help others find it more quickly. If not, please kindly elaborate more. thanks!