Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
SHILL
Frequent Visitor

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

 

1 ACCEPTED SOLUTION
daXtreme
Solution Sage
Solution Sage

 

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_0-1659617032148.png

 

 

View solution in original post

3 REPLIES 3
daXtreme
Solution Sage
Solution Sage

 

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_0-1659617032148.png

 

 

Thanks,Thats worked!

Headline
New Member

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

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.