cancel
Showing results for
Did you mean:
Highlighted
zaidmasad Member

## 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
v-ljerr-msft Super Contributor

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

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
zaidmasad Member

## 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 v-ljerr-msft Super Contributor

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

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

zaidmasad Member

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

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.

v-ljerr-msft Super Contributor

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

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

zaidmasad Member

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

v-ljerr-msft Super Contributor

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

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

zaidmasad Member

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

micky123 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

zaidmasad Member

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