Calculating the percentile for a set of data

[zaidmasad]

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

[zaidmasad]

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]

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

[zaidmasad]

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.

[v-ljerr-msft · Accepted solution]

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

[zaidmasad]

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]

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

[zaidmasad]

Hi @v-ljerr-msft,

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

[zaidmasad]

@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]

@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