Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to 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
I have made this table on power bi, and I need to add next to it the column that calculates the 80th percentile
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
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.
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
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
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
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
@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.
@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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |