Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Saranya3_d
Helper I
Helper I

Percentile

<top> PercentilePercentileHi 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 11
v-diye-msft
Community Support
Community Support

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!

 

 

Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more
quickly.
MFelix
Super User
Super User

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.


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



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 

screenshot of pbixscreenshot of pbix

Hi @Saranya3_d ,

 

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

 

Are you abble to share a file?


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



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

Is it possible to share the excel file?

 

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


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



Miguel Felix,

 

It is available in the below path. Sorry i missed to added it intitally. 

 

https://1drv.ms/u/s!AryZvCsTToGkiFqP1b-OCAsfHXaM

 

thanks,

Saranya

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.


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



Felix,

 

I followed the same steps but the corresponding overall mttr is showing as one hr, two hrs etc., which is not correct. i have placed the pbix file (PP name) , excel sheet (QA resolved dump) & Manual calculation excel sheet for your reference.

 

In the pbix, there are two tabs,

1) Own steps - IN percentile - Initially i have created rules and placed over there (without overall mttr)

2) "Followed steps - IN percentile" - Just followed your steps (overall mttr is not coming properly)

 

Please help me!

 

https://1drv.ms/u/s!AryZvCsTToGkiFIbyzzbWvqt7Pg9?e=R4j3xv

 

Thanks,

Saranya

Felix,

 

I followed the same steps but the corresponding overall mttr is showing as one hr, two hrs etc., which is not correct. i have placed the pbix file (PP name) , excel sheet (QA resolved dump) & Manual calculation excel sheet for your reference.

 

In the pbix, there are two tabs,

1) Own steps - IN percentile - Initially i have created rules and placed over there (without overall mttr)

2) "Followed steps - IN percentile" - Just followed your steps (overall mttr is not coming properly)

 

Please help me!

 

https://1drv.ms/u/s!AryZvCsTToGkiFIbyzzbWvqt7Pg9?e=R4j3xv

 

Thanks,

Saranya

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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