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

     

     

daXtreme's avatar
daXtreme
Icon for Solution Sage rankSolution 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

 

 

  • SHILL's avatar
    SHILL
    Frequent Visitor
    4 years ago

    Thanks,Thats worked!