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

 

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

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

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

 

Capture.JPG

v-ljerr-msft Super Contributor
Super Contributor

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

zaidmasad Member
Member

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.

 

v-ljerr-msft Super Contributor
Super Contributor

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

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

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

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

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