Top 10 Ranked Average

[scaballerom]

Hi all,

I have the following measure:

AvgDuration =

IF(SUM(Fact Jobs'[job_elapsed_min])>0 && ISBLANK([SUM(Fact Jobs'[job_elapsed_min]))=FALSE,AVERAGE('Fact Jobs'[job_elapsed_min]))

What I would like to do is rank the average duration of the processes by day. Then, obtain the average of the top 10 by each day, so I can make a line chart where Y is the avg duration, and X is the date.

I used:

RankAvgDuration =

RANKX (

        SUMMARIZE(ALLSELECTED('Fact Jobs'),'Fact Jobs'[job_name],"@AvgDuration",[AvgDuration]),

        [AvgDuration],

        , ,DENSE

    )

AvgTop10AvgDuration =

VAR AvgDuration =AVERAGEX(

    FILTER(SUMMARIZE(ALLSELECTED('Fact Jobs'),'Fact Jobs'[job_name],"@AvgDuration",[AvgDuration]),LEFT('Fact Jobs'[job_name],3)<>"ZZZ" && [RankAvgDuration]<=10),

    [@AvgDuration])

RETURN

AvgDuration

Sample Data:

Date	job_name	AvgDuration	RankAvgDuration
10/01/2023 0:00	23	121,18	8
10/01/2023 0:00	22	41,99	14
10/01/2023 0:00	19	85,93	11
10/01/2023 0:00	16	34,05	15
10/01/2023 0:00	21	727,72	1
10/01/2023 0:00	16	62,41	12
10/01/2023 0:00	28	93,53	9
10/01/2023 0:00	33	332,12	3
10/01/2023 0:00	26	189,4	6
10/01/2023 0:00	30	197,1	5
10/01/2023 0:00	35	432,85	2
10/01/2023 0:00	26	226,18	4
10/01/2023 0:00	36	52,67	13
10/01/2023 0:00	26	88,65	10
10/01/2023 0:00	21	179,33	7
11/01/2023 0:00	23	127,9	8
11/01/2023 0:00	26	409,68	2
11/01/2023 0:00	16	246,2	3
11/01/2023 0:00	19	91,36	10
11/01/2023 0:00	19	182,83	6
11/01/2023 0:00	25	64,69	14
11/01/2023 0:00	17	65,23	13
11/01/2023 0:00	16	65,89	12
11/01/2023 0:00	30	204,94	5
11/01/2023 0:00	26	71,34	11
11/01/2023 0:00	26	106,69	9
11/01/2023 0:00	26	233,34	4
11/01/2023 0:00	36	53,78	15
11/01/2023 0:00	26	1672,82	1
11/01/2023 0:00	21	171,12	7
12/01/2023 0:00	23	121,89	6
12/01/2023 0:00	19	110,28	7
12/01/2023 0:00	27	85,57	8
12/01/2023 0:00	16	589,89	1
12/01/2023 0:00	16	33,05	12
12/01/2023 0:00	19	188,77	4
12/01/2023 0:00	17	31,77	13
12/01/2023 0:00	30	202,93	3
12/01/2023 0:00	26	72,99	9
12/01/2023 0:00	26	231,45	2
12/01/2023 0:00	36	52,94	11
12/01/2023 0:00	18	63,72	10
12/01/2023 0:00	19	29,13	15
12/01/2023 0:00	22	30,06	14
12/01/2023 0:00	21	180,52	5
13/01/2023 0:00	22	20,14	12
13/01/2023 0:00	22	270,85	3
13/01/2023 0:00	19	300,5	2
13/01/2023 0:00	19	23,86	10
13/01/2023 0:00	17	13,71	14
13/01/2023 0:00	20	51,02	7
13/01/2023 0:00	13	10,47	15
13/01/2023 0:00	17	95,69	4
13/01/2023 0:00	14	14,43	13
13/01/2023 0:00	26	70,92	5
13/01/2023 0:00	36	23,53	11
13/01/2023 0:00	29	26,08	9
13/01/2023 0:00	36	52,8	6
13/01/2023 0:00	26	496,59	1
13/01/2023 0:00	22	30,3	8
14/01/2023 0:00	22	1101,32	1
14/01/2023 0:00	18	0,03	14
14/01/2023 0:00	17	0,04	13
14/01/2023 0:00	16	0,18	6
14/01/2023 0:00	15	0,04	8
14/01/2023 0:00	26	27,97	2
14/01/2023 0:00	26	2,61	5
14/01/2023 0:00	26	5,99	3
14/01/2023 0:00	26	2,63	4
14/01/2023 0:00	26	0,17	7
14/01/2023 0:00	18	0,04	9
14/01/2023 0:00	18	0,04	12
14/01/2023 0:00	19	0,03	15
14/01/2023 0:00	20	0,04	11
14/01/2023 0:00	18	0,04	10
15/01/2023 0:00	21	7,16	15
15/01/2023 0:00	21	7,29	14
15/01/2023 0:00	23	13,9	11
15/01/2023 0:00	32	64,4	6
15/01/2023 0:00	16	27,87	9
15/01/2023 0:00	20	18,91	10
15/01/2023 0:00	19	2182,21	1
15/01/2023 0:00	30	259,77	3
15/01/2023 0:00	26	48,39	8
15/01/2023 0:00	26	179,74	4
15/01/2023 0:00	26	291,19	2
15/01/2023 0:00	19	8,56	13
15/01/2023 0:00	19	9,37	12
15/01/2023 0:00	19	53,6	7
15/01/2023 0:00	21	174,45	5
16/01/2023 0:00	16	26,1	13
16/01/2023 0:00	20	24,62	14
16/01/2023 0:00	19	307,32	2
16/01/2023 0:00	19	27,2	12
16/01/2023 0:00	20	41,59	9
16/01/2023 0:00	17	66,75	7
16/01/2023 0:00	26	22,88	15
16/01/2023 0:00	30	212,48	4
16/01/2023 0:00	26	172,51	5
16/01/2023 0:00	26	240,42	3
16/01/2023 0:00	36	52,12	8
16/01/2023 0:00	26	517,76	1
16/01/2023 0:00	19	37,51	10
16/01/2023 0:00	22	29,7	11
16/01/2023 0:00	21	162,42	6
17/01/2023 0:00	16	343,92	2
17/01/2023 0:00	16	35,48	13
17/01/2023 0:00	19	315,57	3
17/01/2023 0:00	20	47,84	10
17/01/2023 0:00	17	77,1	8
17/01/2023 0:00	26	38,31	12
17/01/2023 0:00	26	42,68	11
17/01/2023 0:00	30	200,02	5
17/01/2023 0:00	26	135,7	7
17/01/2023 0:00	29	28	15
17/01/2023 0:00	26	229,19	4
17/01/2023 0:00	36	51,92	9
17/01/2023 0:00	26	570,75	1
17/01/2023 0:00	22	28,56	14
17/01/2023 0:00	21	192,32	6

Results I would like to obtain:

Date	AvgTop10AvgDuration
10/01/2023 0:00	258,806
11/01/2023 0:00	344,688
12/01/2023 0:00	184,801
13/01/2023 0:00	141,861
14/01/2023 0:00	114,099
15/01/2023 0:00	330,053
16/01/2023 0:00	181,088
17/01/2023 0:00	216,433

 But I had no luck. Could someone help me out, please?

BR,
Sara

[CNENFRNL · Accepted solution]

Simple enough,

[amitchandak]

@scaballerom , Try measure like

AverageX(keepfilters(topn(10, allselected('Fact Jobs'[AvgDuration]) ,_roll18, desc)),[AvgDuration])

or

calculate(AverageX(Values('Fact Jobs'[job_name]),[AvgDuration]),topn(10, allselected('Fact Jobs'[job_name]) ,[AvgDuration], desc))