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 aver...
  • 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

     

     

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" )