Forum Discussion

SHILL's avatar
SHILL
Frequent Visitor
4 years ago
Solved

Count above average

I have the below table in place and need to count how many open cases are above the average time usually taken to close.

I have a measure that is giving me the Average days taken to close (By averaging the difference between recieved/last worked column where the status is also closed) which is working fine.
Now I am trying to get a measure in place to count any cases that are over the average time taken to close but everything I try seems to count all cases regardless of if they are above or below average.

With the below, The outcome should be that the average days to close a case is 17.5 and so I have 1 case over that average.


      
case NameRecieved DateLast Worked OnStatusDifference betwen recieved and last workedcurrent Age (Days)
Test 101/08/202202/08Open14
Test 210/07/202220/07/2022Closed10 
Test 35/07/202230/07/2022Closed25  
Test 427/06/202201/08Open3538

 

  • daXtreme's avatar
    daXtreme
    4 years ago

     

    DEFINE 

MEASURE 'Cases'[Avg Days to Close] = 
CALCULATE(
    AVERAGE( 'Cases'[Diff Received Worked On] ),
    'Cases'[Status] = "closed",
    REMOVEFILTERS( )
)

MEASURE 'Cases'[# Open Cases Above Avg] = 
var AvgDaysToClose = [Avg Days to Close]
var CountOfCasesOfInterest =
if( NOT ISBLANK( AvgDaysToClose ),
    COUNTROWS(
        FILTER(
            'Cases',
            'Cases'[Diff Received Worked On] > AvgDaysToClose
            && 'Cases'[Status] = "open"
        )
    )
)
return
    CountOfCasesOfInterest

     

     

3 Replies

  • Headline's avatar
    Headline
    New Member
    4 years ago

    You can use 

    DATEDIFF(FIRSTDATE(columd(date)]),TODAY(),MONTH) after that If the result is greater than your limited number, use a new if formula = if ( result>="Your limited number","closed case","Open case" )
  • daXtreme's avatar
    daXtreme
    Solution Sage
    4 years ago

     

    DEFINE 

MEASURE 'Cases'[Avg Days to Close] = 
CALCULATE(
    AVERAGE( 'Cases'[Diff Received Worked On] ),
    'Cases'[Status] = "closed",
    REMOVEFILTERS( )
)

MEASURE 'Cases'[# Open Cases Above Avg] = 
var AvgDaysToClose = [Avg Days to Close]
var CountOfCasesOfInterest =
if( NOT ISBLANK( AvgDaysToClose ),
    COUNTROWS(
        FILTER(
            'Cases',
            'Cases'[Diff Received Worked On] > AvgDaysToClose
            && 'Cases'[Status] = "open"
        )
    )
)
return
    CountOfCasesOfInterest