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.
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 |
Solved! Go to 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.
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.
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 |
---|---|
112 | |
99 | |
73 | |
72 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |