Forum Discussion

Saranya3_d's avatar
Saranya3_d
Icon for Helper I rankHelper I
6 years ago

Percentile

<top>  Hi 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 ...
MFelix's avatar
MFelix
Icon for Super User rankSuper User
6 years ago

Hi Saranya3_d ,

 

You can you share a file trough onedrive, googledrive, wetransfer or similar links.

 

Are you abble to share a file?

Saranya3_d's avatar
Saranya3_d
Icon for Helper I rankHelper I
6 years ago

Mflexi,

 

Pbix file has been placed in the below drive. Please help me to arrive the precentile. 

 

https://1drv.ms/u/s!AryZvCsTToGkiFbVLcHPg7eaneEr

 

PS:  I will write a query in column to change the "Overall MTTR in Hours" into "HH:MM:SS" format. All the data's are available in the single table like Priority, Ticket ID (unique filed), Ovearll MTTR in hours, overall mttr in min, created and resolved time. 

 

Thanks,

Saranya

  • MFelix's avatar
    MFelix
    Icon for Super User rankSuper User
    6 years ago

    Is it possible to share the excel file?

     

    I need to make some changes in order not having duplicated rankings.

      • MFelix's avatar
        MFelix
        Icon for Super User rankSuper User
        6 years ago

        Hi Saranya3_d .

         

        What I did was the following:

        • Added an index column to the data (this is to have only unique values on the ranking)
        • Created a calculated column equal to MTTR IN min and formatted as number (if you want to have the MTTR in hours this shuld be that column)
        • Created a ranking column
        Column Ranking = 
RANKX (
    fILTER ( Sheet1; Sheet1[Priority] = EARLIER ( Sheet1[Priority] ) );
    Sheet1[ID_Sorting] - [Index] / POWER ( 10; 9 );
    ;
    asc;
    DENSE
)

         

        • Created the following measure:
        P95 MTTR = 
var Percentile_value = [P95 Count]
Return
CALCULATE (MAX(Sheet1[Overall MTTR in Min]);
  FILTER (
        ALL(Sheet1);
        Sheet1[Column Ranking] = Percentile_value
            && Sheet1[Priority] = MAX ( Sheet1[Priority] )
    )
)

         

        Check PBIX file attach, and tell me if this is the expected result.