Count as a Percentage

[shakeeb_mohammed]

Hi Can someone assist with the below please?

Sample data :

Trip	Delivery Point	Delivery Status
001	Derby	OnTime
001	Nottingham	Late
002	Nottingham	OnTime
002	Birmingham	Late
002	Derby	OnTime
003	Birmingham	OnTime
003	Derby	Late

I need to somehow show how many Distinct Delivery Point to a Trip were on OnTime or Late..

E.g. There has been a total of 7 Departures - 4 of them were OnTime and 3 of them were Late

Derby had 2 OnTime for 2 trips and 1 Late for 1 Trip.

The Idea is so i can show this as a percentage - Count Ontime by Delivery Point / Total Distinct Trips

Hope this makes sense!!

[OmarBenSalem]

U want the count by trip or delivery pioint?

Could u create a table with the output u want as u've done for the input data?

[shakeeb_mohammed]

Location	OnTime %	Late %
Derby	75%	25%
Birmingham	60%	40%
Manchester	70%	30%

I need to show how many delivery points are in 1 trip and its percentage by location.

[OmarBenSalem]

Still can't figure out how Derby is 75% OnTime ?

[OmarBenSalem]

wouldn't this be more "accurate"? Mayube i'm missing sthing!

[shakeeb_mohammed]

Sorry-

Location	No. Visits	OnTime %	Late %
Nottingham	2	50%	50%
Derby	3	67%	33%
Birmingham	2	50%	50%

[OmarBenSalem · Accepted solution]

on time : =count({<[Delivery Status]={'OnTime'}>}Trip)/count(distinct Trip)

Late : =count({<[Delivery Status]=-{'OnTime'}>}Trip)/count(distinct Trip)

No Visits : count(distinct Trip)

result:

[shakeeb_mohammed]

How come my percentages are showing way out?

[OmarBenSalem]

what dimension and measure u're using?

It's not clear in the image

[shakeeb_mohammed]

I had to use distinct twice as the data I'm using had more that 1 trip number.

I used

=count({<[DeliveryStatusByBookingTime]={'OnTime'}>}Distinct Trip)/count(distinct Trip)