cancel
Showing results for
Search instead for
Did you mean:
Highlighted
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.

 ID Origin Code Transit Time IMAE5701 STO PPX 3 IMAE5702 LON PPX 2 IMAE5703 RMM PDX IMAE5704 BEY DPX 0 IMAE5705 HAX PPX 3 IMAE5706 JFK PPX 2 IMAE5707 BEY PDX 0 IMAE5708 PNQ PPX 0 IMAE5709 HKG PPX 2 IMAE5710 AMD PPX 0 IMAE5711 HKG PPX 2 IMAE5712 LON PPX 2

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

 Country ProductCode 80th Perc. STO PPX 1 day LON PPX 4 days RMM PDX 3 days
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
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:

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

Regards

9 REPLIES 9
Highlighted
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

Highlighted
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?

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

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

Regards

Highlighted
Helper III

## Re: Calculating the percentile for a set of data

thank for your reply @v-ljerr-msft

the below error message appeared:

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

## 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:

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

Regards

Highlighted
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

## 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?

Regards

Highlighted
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

## Re: Calculating the percentile for a set of data

I want to have something as the below:

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

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

#### Power Platform Community Conference

Check out the on demand sessions that are available now!

#### Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

#### 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