Calculating the average of max values hit across a week

[Frasermc98]

I obtain a daily measure of a top speed that a person hits in training during a session. I also have a date table set up that contains the weeks of the season. I am wondering if it is possible to calculate in DAX the max speed hit across the week and then average this max speed hit across the weeks. 

Any help in doing this would be greatly appreciated. 

Thanks. 

[Anonymous · Accepted solution]

Hi @Frasermc98 

Please try this:

First of all, I create a set of sample data:

The week number is created by 

week number = WEEKNUM('Sheet1'[date])

Then I crreate a measure and create a matrix with it:

max of speed = MAX('Sheet1'[speed])

After that, I create a measure to count the average value of the max speed:

average of speed = 
	VAR _MAX = ADDCOLUMNS(
		ALLSELECTED('Sheet1'),
		"_MAXSPEED", CALCULATE(
			MAX('Sheet1'[speed]),
			FILTER(
				ALLSELECTED('Sheet1'),
				'Sheet1'[week number] = EARLIER('Sheet1'[week number])
			)
		)
	)
	RETURN
		AVERAGEX(
			_MAX,
			[_MAXSPEED]
		)

The result is as follow:

Best Regards,

Zhengdong Xu

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

[audreygerred]

Hello! You will want to use an interator function here: AVERAGEX

http://powerbiwithme.com/2023/11/01/the-iterator-edition/

AVERAGEX function (DAX) - DAX | Microsoft Learn

Try:

AverageOfMaxValues = AVERAGEX( VALUES('YourTable'[GroupColumn]), CALCULATE( MAX('YourTable'[ValueColumn]) ) )