How to remove top 20% from average

[Cma2]

Hi, I've actually seen a post before that more or less asked exactly the same quesiton but it never got answered.

Edit: Sorry for the repost. My original example created too simple of a case and the solution reply did not fully encapsulate what I needed. The topic is also gone for some reason...Anyhow in the original table, I need not include a date. For my purposes, the data are grouped by month. Therefore I can't just use the full table index > _cutoff20 solution below.

This is for a call center where the top 20% of the queue time can be removed from the average calculation. Take the table below for example, if we calculate all the queue time averages, we would get an average for Feb: 32.8 and Mar: 31.3. However, because we can take away the top 20%, the 83 and 100 from Feb, and 60 and 90 from March will not be included. With that, the average we should be getting is 18.125 in Feb, and 20.375. Can I please get some help and be pointed in the right direction for this? Thank you in advance.

Node ID		Date			Queue Time
46002135883		01-Feb			0
46002135907		02-Feb			2
46002135933		03-Feb			1
46002136382		04-Feb			15
46002136558		05-Feb			8
46002138955		06-Feb			24
46002139683		07-Feb			30
46002136372		08-Feb			83
46002139689		09-Feb			100
46002139691		10-Feb			65
46002139692		01-Mar			13
46002139693		02-Mar			18
46002139694		03-Mar			60
46002139695		04-Mar			3
46002139696		05-Mar			4
46002139697		06-Mar			90
46002139698		07-Mar			50
46002139701		08-Mar			24
46002139702		09-Mar			36
46002139653		10-Mar			15

[Cma2]

Hi, I've actually seen a post before that more or less asked exactly the same quesiton but it never got answered.

Edit: Sorry for the repost. My original example created too simple of a case and the solution reply did not fully encapsulate what I needed. The topic is also gone for some reason...Anyhow in the original table, I need not include a date. For my purposes, the data are grouped by month. Therefore I can't just use the full table index > _cutoff20 solution below.

This is for a call center where the top 20% of the queue time can be removed from the average calculation. Take the table below for example, if we calculate all the queue time averages, we would get an average for Feb: 32.8 and Mar: 31.3. However, because we can take away the top 20%, the 83 and 100 from Feb, and 60 and 90 from March will not be included. With that, the average we should be getting is 18.125 in Feb, and 20.375. Can I please get some help and be pointed in the right direction for this? Thank you in advance.

Node ID		Date			Queue Time
46002135883		01-Feb			0
46002135907		02-Feb			2
46002135933		03-Feb			1
46002136382		04-Feb			15
46002136558		05-Feb			8
46002138955		06-Feb			24
46002139683		07-Feb			30
46002136372		08-Feb			83
46002139689		09-Feb			100
46002139691		10-Feb			65
46002139692		01-Mar			13
46002139693		02-Mar			18
46002139694		03-Mar			60
46002139695		04-Mar			3
46002139696		05-Mar			4
46002139697		06-Mar			90
46002139698		07-Mar			50
46002139701		08-Mar			24
46002139702		09-Mar			36
46002139653		10-Mar			15

[v-yangliu-msft · Accepted solution]

Hi  @Cma2  ,

Here are the steps you can follow：

1. Create calculated colum.

rank =
RANKX(FILTER(ALL('Table'),MONTH('Table'[Date])=MONTH(EARLIER('Table'[Date]))),'Table'[Queue Time],,DESC)

2. Create measure.

Measure =
var
_maxrank=CALCULATE(MAX('Table'[rank]),FILTER(ALL('Table'),MONTH([Date])=MONTH(MAX('Table'[Date]))))
return
CALCULATE(AVERAGE('Table'[Queue Time]),FILTER(ALL('Table'),MONTH([Date])=MONTH(MAX('Table'[Date]))&&[rank]>DIVIDE(_maxrank,5)))

3. Result

You can downloaded PBIX file from here.

Best Regards,

Liu Yang

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