Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
<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 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
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!
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.
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 |
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
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi 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
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
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsMflexi,
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
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsMiguel 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:
Column Ranking =
RANKX (
fILTER ( Sheet1; Sheet1[Priority] = EARLIER ( Sheet1[Priority] ) );
Sheet1[ID_Sorting] - [Index] / POWER ( 10; 9 );
;
asc;
DENSE
)
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
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsFelix,
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
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |