Average of top 5 values

[arp2]

Team, I want a measure to calculate the Average of the top 5 Values in the below table. Any suggestions plz!

Week		Forecast Hours
1-Feb		45
8-Feb		48
15-Feb		45
22-Feb		20
29-Feb		25
7-Mar		45
14-Mar		49
21-Mar		25
28-Mar		28
4-Apr		27
11-Apr		26
18-Apr		24
25-Apr		21

[zenisekd · Accepted solution]

Hi, try: 

Top5_Average = AVERAGEX( TOPN(5, ALL('YourTableName'[Forecast Hours]), 'YourTableName'[Forecast Hours], DESC), 'YourTableName'[Forecast Hours] )

Kudos and mark as solution appreciated.

[_AAndrade]

Hi,

Use this formula:

If you want to check that the result is correct, change AVERAGEX to SUMX and check that the numbers are correct.
On my side, my formula gives me the correct numbers. Unfortunately, zenisekd's formula is not giving the correct numbers

[arp2]

@_AAndrade 

Can you plz help on this? I want a measure which will sum all the records per week and then take average of top 5.

Week		Discipline		Project		Forecast Hours
45323		Disc1		Project1		45
45330		Disc1		Project1		48
45337		Disc1		Project1		45
45344		Disc1		Project1		20
45351		Disc1		Project1		25
45358		Disc1		Project1		45
45365		Disc1		Project1		49
45372		Disc1		Project1		25
45379		Disc1		Project1		28
45386		Disc1		Project1		27
45393		Disc1		Project1		26
45400		Disc1		Project1		24
45407		Disc1		Project1		21
45323		Disc1		Project2		49
45330		Disc1		Project2		37
45337		Disc1		Project2		35
45344		Disc1		Project2		54
45351		Disc1		Project2		36
45358		Disc1		Project2		42
45365		Disc1		Project2		40
45372		Disc1		Project2		35
45379		Disc1		Project2		36
45386		Disc1		Project2		35
45393		Disc1		Project2		36
45400		Disc1		Project2		37
45407		Disc1		Project2		51
45323		Disc1		Project3		51
45330		Disc1		Project3		49
45337		Disc1		Project3		52
45344		Disc1		Project3		46
45351		Disc1		Project3		56
45358		Disc1		Project3		58
45365		Disc1		Project3		64
45372		Disc1		Project3		53
45379		Disc1		Project3		64
45386		Disc1		Project3		47
45393		Disc1		Project3		48
45400		Disc1		Project3		64
45407		Disc1		Project3		53

[_AAndrade]

Use this two measures:

Final result:

[arp2]

@_AAndrade  What is "@totWeek"? I am trying the same but don't get it

[_AAndrade]

@arp2 It's one variable created on the summarize function, that represents the SUM the forecast hours by week

[arp2]

@_AAndrade  Can you plz check what wrong am I doing? 

[_AAndrade · Accepted solution]

The problem is caused by using my measures in a calculated columns instead of using as a measure.
Use mearues instead of calculated columns and it will works fine.

[arp2]

Thanks @_AAndrade  It's working perfectly after correcting my mistake. Great help indeed!

[zenisekd · Accepted solution]

Hi, try: 

Top5_Average = AVERAGEX( TOPN(5, ALL('YourTableName'[Forecast Hours]), 'YourTableName'[Forecast Hours], DESC), 'YourTableName'[Forecast Hours] )

Kudos and mark as solution appreciated.

[arp2]

Thanks @zenisekd this is helpful. If I have multiple records for the each week, how to calculate average of aggregated number per week?

Week		Discipline		Project	Forecast Hours
1-Feb		Disc1		Project1	45
8-Feb		Disc1		Project1	48
15-Feb		Disc1		Project1	45
22-Feb		Disc1		Project1	20
29-Feb		Disc1		Project1	25
7-Mar		Disc1		Project1	45
14-Mar		Disc1		Project1	49
21-Mar		Disc1		Project1	25
28-Mar		Disc1		Project1	28
4-Apr		Disc1		Project1	27
11-Apr		Disc1		Project1	26
18-Apr		Disc1		Project1	24
25-Apr		Disc1		Project1	21
1-Feb		Disc1		Project2	50
8-Feb		Disc1		Project2	50
15-Feb		Disc1		Project2	35
22-Feb		Disc1		Project2	49
29-Feb		Disc1		Project2	44
7-Mar		Disc1		Project2	55
14-Mar		Disc1		Project2	42
21-Mar		Disc1		Project2	48
28-Mar		Disc1		Project2	54
4-Apr		Disc1		Project2	49
11-Apr		Disc1		Project2	54
18-Apr		Disc1		Project2	51
25-Apr		Disc1		Project2	40
1-Feb		Disc1		Project3	64
8-Feb		Disc1		Project3	46
15-Feb		Disc1		Project3	53
22-Feb		Disc1		Project3	48
29-Feb		Disc1		Project3	54
7-Mar		Disc1		Project3	53
14-Mar		Disc1		Project3	54
21-Mar		Disc1		Project3	64
28-Mar		Disc1		Project3	54
4-Apr		Disc1		Project3	45
11-Apr		Disc1		Project3	53
18-Apr		Disc1		Project3	65
25-Apr		Disc1		Project3	45

[zenisekd]

Try:
AverageForecastHoursPerWeek =
VAR WeeklySummary = SUMMARIZE ( Table1, Table1[Week], "Total Forecast Hours", SUM ( Table1[Forecast Hours] ) )
RETURN AVERAGEX ( WeeklySummary, [Total Forecast Hours] / COUNTROWS ( WeeklySummary ) )