Count Ids above Average

[Wilbert]

I have the following sample table where I am required to calculate the average time for each parameter.

For ids where date closed is populated

Time = Date Closed – Date Open

For ids where date closed is blank

Time = max(Date Updated) – Date Open

Parameters	ID	Date Open	Date Closed	Date Updated
Temperature	AB	5/13/2017	12/16/2017	12/18/2017
Wind force	AD	6/8/2018		10/10/2018
Wind force	AD	6/8/2018		11/10/2018
Humidity	AC	3/10/2018	1/2/2019	2/7/2019
Temperature	AG	8/15/2018		11/20/2018
Humidity	AH	5/3/2018		8/19/2018
Humidity	AH	5/3/2018		9/2/2018
Wind force	AO	7/23/2018		7/29/2018

I am required to find the average time taken for each parameter and the no. of ids which are above the average time. My final Table should look something like this:

Cases	Average Time	No of IDs Above Average
Temperature	157	1
Humidity	210.5	1
Wind force	81	1

[sunny_talwar]

May be try this

Avg(Aggr(
 If(Len(Trim([Date Closed])) > 0, 
  ([Date Closed] - [Date Open]),
  (Max(TOTAL <ID> [Date Updated]) - [Date Open])
 )
, Parameters, ID))

[Wilbert]

Hi Sunny,

Thanks for the response. However, I am looking to count the number of  IDs against each parameter that are above the average calculated (last column of my result table). 

[sunny_talwar · Accepted solution]

Try this for Count of IDs

Count(Aggr(
	If(Avg(TOTAL <Parameters> Aggr(If(Len(Trim([Date Closed])) > 0, ([Date Closed] - [Date Open]), (Max(TOTAL <ID> [Date Updated]) - [Date Open])), Parameters, ID)) > If(Len(Trim([Date Closed])) > 0, ([Date Closed] - [Date Open]), (Max(TOTAL <ID> [Date Updated]) - [Date Open])), ID)
, ID, Parameters))

or

Count(DISTINCT Aggr(
	If(Avg(TOTAL <Parameters> Aggr(If(Len(Trim([Date Closed])) > 0, ([Date Closed] - [Date Open]), (Max(TOTAL <ID> [Date Updated]) - [Date Open])), Parameters, ID)) > If(Len(Trim([Date Closed])) > 0, ([Date Closed] - [Date Open]), (Max(TOTAL <ID> [Date Updated]) - [Date Open])), ID)
, ID, Parameters))

[Wilbert]

Thanks  Sunny