Count of Items where time is over the average

[Anonymous]

Hi All, 

I've tried searching for this, and for the life of me can't find it, so im hoping someone can help.

I have 3 projects, all with varying durations.  I need to be able to calculate the number of projects where the duration is OVER the average of all projects.

Example:

Project Title	Duration (Hours)
Sample Project 1	.01
Sample Project 2	.02
Sample Project 3	1.5

The Average duration for the 3 projects is 0.51 hours.   

The desired outcome here is a meausre that shows 1.  1 project has a duration that is over the average.  

any help here would be amazing.. ive been struggling mightly to make this work.

Thank you!

[Anonymous]

Hi @Anonymous ,

Please use the following formula to create measures:

Flag =
VAR _SumOfEachProject =
    CALCULATE (
        SUM ( 'Table'[Duration (Hours)] ),
        ALLEXCEPT ( 'Table', 'Table'[Project Title] )
    )
VAR _avg =
    AVERAGEX ( ALL ( 'Table' ), [Duration (Hours)] )
RETURN
    IF ( _SumOfEachProject > _avg, 1 )

DistinctCount =
CALCULATE (
    DISTINCTCOUNT ( 'Table'[Project Title] ),
    FILTER ( 'Table', [Flag] = 1 )
)

The final output is shown below:

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Ashish_Mathur]

Hi,

How will one differentiate between the 3 projects when all have the same name.

[Anonymous]

My apologies. They were supposed to be 1,2&3. Too much of a rush. 

I have  adjusted the original post to as it should be 

[Ashish_Mathur · Accepted solution]

Hi,

Write these measures

Total = sum(Data[Duration (Hours)])

Average = average(Data[Duration (Hours)])

Measure1 = countrows(filter(values(Data[Project title]),[total]>calculate([average],all(Data[Project title]))))

Drag measure1 to the card visual.

[Anonymous]

This worked perfectly, thank you!

[Ashish_Mathur]

You are welcome.