cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper III
Helper III

Calculating the percentile for a set of data

I have a set of data as below, and I want to calculate the 80th percentile for it using power bi, I want to see what is the transit time for 80% of the data.

 

IDOriginCodeTransit Time
IMAE5701STOPPX3
IMAE5702LONPPX2
IMAE5703RMMPDX 
IMAE5704BEYDPX0
IMAE5705HAXPPX3
IMAE5706JFKPPX2
IMAE5707BEYPDX0
IMAE5708PNQPPX0
IMAE5709HKGPPX2
IMAE5710AMDPPX0
IMAE5711HKGPPX2
IMAE5712LONPPX2

 

Anyone can help me with this? below is an example how the results should be

 

CountryProductCode80th Perc.
STOPPX1 day
LONPPX4 days
RMMPDX3 days
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Microsoft
Microsoft

Re: Calculating the percentile for a set of data

Hi @zaidmasad,

 

You need to use the formula to create a measure instead of a calculate column. See:

 

m1.PNG

 

Then you should be able to show the measure with the country and city column on the Table visual on the report.

 

Regards

View solution in original post

9 REPLIES 9
Highlighted
Helper III
Helper III

Re: Calculating the percentile for a set of data

I have made this table on power bi, and I need to add next to it the column that calculates the 80th percentile

 

Capture.JPG

Highlighted
Microsoft
Microsoft

Re: Calculating the percentile for a set of data

Hi @zaidmasad,

 

Could you try using the formula below to create a new measure, then show it on the table visual to see if it works in your scenario? Smiley Happy

Measure = PERCENTILE.EXC(Table1[Transit Time],0.8)

Note: just replace 'Table1' with your real table name.

 

Regards

Highlighted
Helper III
Helper III

Re: Calculating the percentile for a set of data

thank for your reply @v-ljerr-msft 

 

the below error message appeared:

Capture.JPG

 

I need to calculate for each country and city the 80th percentile, not for each request.

 

So for example I will have a result as shown below:

 

Country     City       Percentile

UAE           DXB        5 days

UAE           AUH       2 days

JO             AMM      1 day

USA            NY        3 days

 

the function i want will take all the requests for each origin country and city and calculate the 80th percentile for them.

 

Appreciate your help.

 

Highlighted
Microsoft
Microsoft

Re: Calculating the percentile for a set of data

Hi @zaidmasad,

 

You need to use the formula to create a measure instead of a calculate column. See:

 

m1.PNG

 

Then you should be able to show the measure with the country and city column on the Table visual on the report.

 

Regards

View solution in original post

Highlighted
Helper III
Helper III

Re: Calculating the percentile for a set of data


Hi @v-ljerr-msft, is it possbile to visualize the measure after creating it? Now the table is working properly, but I cant add the measure to line chart for example.

 

Thank you

Highlighted
Microsoft
Microsoft

Re: Calculating the percentile for a set of data

Hi @zaidmasad,

 

It should be possible. But it will depend on what column you're showing on the Axis of the line chart. So what column are you showing on the Axis? Smiley Happy

 

Regards

Highlighted
Helper III
Helper III

Re: Calculating the percentile for a set of data

@v-ljerr-msft i want to have the request date as an axis, and the values are the average of the 80th percentile for each country and city. I tried all option to add the calculated measure to the chart but its not working, I just can add it to the tooltips but this is not useful.

Highlighted
Frequent Visitor

Re: Calculating the percentile for a set of data

@zaidmasad this solution is very useful! How would I use this to create multiple percentiles?
For example I would like to have percentiles calculated for all below:

0
0.01
0.02
0.03
0.04
0.05
0.10
0.15
0.20
0.25
0.30
0.35
0.40
0.45
0.50
0.55
0.60
0.65
0.70
0.75
0.80
0.85
0.90
0.95
0.96
0.97
0.98
0.99
1.00

 

I was wondering how can I adapt your solution to achieve this?


Kind Regards

Highlighted
Helper III
Helper III

Re: Calculating the percentile for a set of data

Hi @v-ljerr-msft,

 

I want to have something as the below:

 

Capture.JPG

 

I have a filter for origin and destination, so this should be changed on the applied filters. T1, T2,T3 are the 80th percentile of certain KPIs. 

 

I have used the measure to calculate the 80th percentile as you suggested before.

 

Thanks,

Zaid

 

Helpful resources

Announcements
Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors