Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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!
Solved! Go to 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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
User | Count |
---|---|
93 | |
84 | |
78 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
60 |