Calculate Average measure based on the similar age of teams

[uniqum47]

I feel like it should be an easy solution, but can't quite figure out after hours of googling 😞

I have a Team slicer. 

I need to calculate averge for GIQ based on chosen in slicer Team' age +-10 days

Have following data set:

Organisation	Team	TeamAge	GIQ
X	AX	200	168
O	B	205	150
X	CX	200	130
Y	D	150	105
X	EX	195	190
Z	F	50	150

When we chose Team AX, the answer should be the average of GIQ for (AX, B, CX and EX ) teams.

I have been trying DAX, but if there is solution in M - I will be happy to use it too.

Thank you!

[mahoneypat · Accepted solution]

Please try this measure expression.

Avg Within 10 =
VAR thisage =
    MIN ( Ages[TeamAge] )
VAR result =
    CALCULATE (
        AVERAGE ( Ages[GIQ] ),
        ALL ( Ages ),
        FILTER (
            ALL ( Ages[TeamAge] ),
            Ages[TeamAge] >= thisage - 10
                && Ages[TeamAge] <= thisage + 10
        )
    )
RETURN
    result

Pat

[tex628]

Hello @uniqum47,

Try this:

Measure = 
VAR Age = SELECTEDVALUE(Table[TeamAge]
VAR upperlimit = Age + 10
VAR lowerlimit = Age - 10
Return 
AVERAGEX(FILTER(ALL(TABLE), Table[TeamAge] >= lowerlimit && Table[TeamAge] <= upperlimit ) , Table[GIQ] )
 

Let me know how it goes! 

Br, 
J