Mean, median and percentile

[Robot123]

Hi everyone,

I am new to PBI and need help.

My team receives service requests and completes them.  While working on the requests, we may reach out to some other departments.  Each time we reach out, we record the leadtime the department takes to come back.

By country and month, I would like to find out the following:

what are the mean and median distinct count of requests that we needed to reach out to other departments?
what are the mean and median count we reached out to other departments per request?
what are the mean and median response time (sum of dept_leadtime in request) per department?
What are the mean and median response time (sum of dept_leadtime in request) per department at 90percentile of response time?

Honestly, am not clear if I am making sense...

Hope you can help me.

Thank you.

Request ID	Country	Closed date	Dept	Dept_Leadtime (Hr)
8409950	NZ	04-Jul-23	OP	0.011388889
8409950	NZ	04-Jul-23	OP	0.941944444
8409950	NZ	04-Jul-23	OP	0.123333333
8409950	NZ	04-Jul-23	OP	0.088333333
8461598	AUS	19-Sep-23	OP	0.323611111
8461598	AUS	19-Sep-23	OP	0.16
8461598	AUS	19-Sep-23	SR	24.75972222
8461598	AUS	19-Sep-23	OP	0.139722222
8461598	AUS	19-Sep-23	OP	0.002222222
8461598	AUS	19-Sep-23	SR	0.053888889
8461598	AUS	19-Sep-23	OP	0.787222222
8461598	AUS	19-Sep-23	OP	0.005277778
8461598	AUS	19-Sep-23	SR	32.42527778
8461598	AUS	19-Sep-23	OP	0
8461598	AUS	19-Sep-23	OP	0
8461598	AUS	19-Sep-23	SR	0
8461598	AUS	19-Sep-23	OP	28.22472222
8461598	AUS	19-Sep-23	OP	0
8461598	AUS	19-Sep-23	OP	0
8461598	AUS	19-Sep-23	SR	14.64888889
8461598	AUS	19-Sep-23	OP	0.503333333
8461598	AUS	19-Sep-23	OP	0.005555556
8461598	AUS	19-Sep-23	SR	0.842222222
8461598	AUS	19-Sep-23	OP	32
8461598	AUS	19-Sep-23	OP	0.0125
8461598	AUS	19-Sep-23	SR	17.41555556
8461598	AUS	19-Sep-23	OP	0.391666667
8461598	AUS	19-Sep-23	OP	0.167222222
8461598	AUS	19-Sep-23	SR	19.51944444
8461598	AUS	19-Sep-23	OP	0
8461598	AUS	19-Sep-23	SR	15.8025
8461598	AUS	19-Sep-23	OP	0.076388889
8461598	AUS	19-Sep-23	OP	0.042222222
8461598	AUS	19-Sep-23	SR	0.078888889
8461598	AUS	19-Sep-23	OP	0.660833333
8461598	AUS	19-Sep-23	OP	0.131388889
8461598	AUS	19-Sep-23	OP	0.039722222
8461598	AUS	19-Sep-23	SR	16.51888889
8461598	AUS	19-Sep-23	OP	2.514722222
8461598	AUS	19-Sep-23	OP	0.0025
8461598	AUS	19-Sep-23	SR	0.133888889
8461598	AUS	19-Sep-23	OP	2.2875
8461598	AUS	19-Sep-23	OP	0.003611111
8461598	AUS	19-Sep-23	OP	0.080277778
8461598	AUS	19-Sep-23	OP	26.65527778
8461598	AUS	19-Sep-23	OP	22.01805556
8461598	AUS	19-Sep-23	OP	0.263611111
8461598	AUS	19-Sep-23	OP	0.0125
8461598	AUS	19-Sep-23	SR	19.75416667
8461598	AUS	19-Sep-23	OP	0.009722222
8461598	AUS	19-Sep-23	SR	4.6875
8461598	AUS	19-Sep-23	OP	0.130555556
8461598	AUS	19-Sep-23	SR	13.47416667
8461598	AUS	19-Sep-23	OP	17.17722222
8461598	AUS	19-Sep-23	OP	0.2375
8461598	AUS	19-Sep-23	OP	0.005277778
8461598	AUS	19-Sep-23	SR	12.14694444
8461598	AUS	19-Sep-23	OP	0.006666667
8461598	AUS	19-Sep-23	SR	14.88722222
8461598	AUS	19-Sep-23	OP	0.478055556
8461598	AUS	19-Sep-23	OP	0.211944444
8461598	AUS	19-Sep-23	SR	15.51666667
8552256	AUS	07-Sep-23	OP	0
8552256	AUS	07-Sep-23	OP	0
8552256	AUS	07-Sep-23	SR	0
8552256	AUS	07-Sep-23	OP	4.355
8552256	AUS	07-Sep-23	OP	0.003888889
8552256	AUS	07-Sep-23	OP	1.236388889
8552256	AUS	07-Sep-23	OP	0.208333333
8552256	AUS	07-Sep-23	OP	0.003055556
8552256	AUS	07-Sep-23	SR	42.75805556
8552256	AUS	07-Sep-23	OP	0.041388889
8552256	AUS	07-Sep-23	OP	0.134444444
8552256	AUS	07-Sep-23	SR	16.55805556
8552256	AUS	07-Sep-23	OP	2.658055556
8552256	AUS	07-Sep-23	OP	0.003055556
8552256	AUS	07-Sep-23	SR	4.271111111
8552256	AUS	07-Sep-23	OP	0.003333333
8552256	AUS	07-Sep-23	SR	1.064444444
8552256	AUS	07-Sep-23	OP	0
8552256	AUS	07-Sep-23	SR	3.644722222
8552256	AUS	07-Sep-23	OP	0.036388889
8552256	AUS	07-Sep-23	OP	0.011111111
8552256	AUS	07-Sep-23	SR	0.488055556
8552256	AUS	07-Sep-23	OP	0.0175
8552256	AUS	07-Sep-23	OP	0.061944444
8552256	AUS	07-Sep-23	SR	51.74027778
8559805	AUS	08-Sep-23	OP	0
8559805	AUS	08-Sep-23	OP	0
8559805	AUS	08-Sep-23	SR	12.23694444
8559805	AUS	08-Sep-23	OP	0.038888889
8559805	AUS	08-Sep-23	OP	0.032777778
8559805	AUS	08-Sep-23	OP	1.681666667
8559805	AUS	08-Sep-23	OP	0.106666667
8559805	AUS	08-Sep-23	OP	0.009722222
8559805	AUS	08-Sep-23	SR	6.251666667
8559805	AUS	08-Sep-23	OP	1.505833333
8559805	AUS	08-Sep-23	OP	0.003055556
8559805	AUS	08-Sep-23	SR	21.57055556
8559805	AUS	08-Sep-23	OP	0
8559805	AUS	08-Sep-23	OP	0
8559805	AUS	08-Sep-23	SR	8
8559805	AUS	08-Sep-23	OP	2.788611111
8559805	AUS	08-Sep-23	OP	0.004722222
8559805	AUS	08-Sep-23	SR	1.674722222
8559805	AUS	08-Sep-23	OP	0.01
8559805	AUS	08-Sep-23	OP	0.055
8559805	AUS	08-Sep-23	OP	0.002777778
8559805	AUS	08-Sep-23	SR	19.02055556
8559805	AUS	08-Sep-23	OP	0.087777778
8559805	AUS	08-Sep-23	OP	0.024166667
8559805	AUS	08-Sep-23	SR	48.33166667
8409950	NZ	04-Jul-23	OP	0.011388889
8409950	NZ	04-Jul-23	OP	0.941944444
8409950	NZ	04-Jul-23	OP	0.123333333
8409950	NZ	04-Jul-23	OP	0.088333333

[Anonymous · Accepted solution]

Hi @Robot123 ,

According to your description, here are my steps you can follow as a solution.

(1) My test data is the same as yours.

(2) We can create measures. 

90th Percentile of Response Time per Department = PERCENTILEX.INC('Table', 'Table'[Dept_Leadtime (Hr)], 0.9)

Mean Distinct Count = AVERAGEX('Table',DISTINCTCOUNT('Table'[Request ID]))

Mean Response Time = AVERAGE('Table'[Dept_Leadtime (Hr)])

Mean Response Time at 90th Percentile = AVERAGEX('Table',[90th Percentile of Response Time per Department])

Median Distinct Count = MEDIANX('Table',DISTINCTCOUNT('Table'[Request ID]))

Median Response Time = MEDIAN('Table'[Dept_Leadtime (Hr)])

Median Response Time at 90th Percentile = MEDIANX('Table',[90th Percentile of Response Time per Department])

Sum of Dept_Leadtime per Department = SUM('Table'[Dept_Leadtime (Hr)])

(3) Then the result is as follows.

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

[Unknowncharacte]

Try

=CALCULATE(MEDIAN('Table Name'[column name])