Forum Discussion

Cma2's avatar
Cma2
Regular Visitor
5 years ago
Solved

How to remove top 20% from average

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
Need Help
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    5 years ago

    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.

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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.