avgtopn
1 Topic
Top 10 Ranked Average
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, SaraSolved843Views0likes2Comments