Forum Discussion
Solved
Subtracting Averages from a table across columns
I'm wrecking my brain trying to figure out something that seems simple. I have a data set that has a group of teams from which I have generated a measure for the team Avg that I can display in the f...
am-person I could be wrong but this is what I think the result should be:
Avg = AVERAGE ( 'Team'[pts] ) Team Avg = CALCULATE ( [Avg], ALLSELECTED ( 'Team'[opponent] ) ) Avg Diff = VAR vTeamAvg = [Team Avg] VAR vOpponentAvg = CALCULATE ( [Team Avg], ALL ( 'Team' ), TREATAS ( VALUES ( 'Team'[Opponent] ), 'Team'[Team] ) ) VAR vResult = vTeamAvg - vOpponentAvg RETURN vResult
DataInsights
Super User
Super User
Try this measure. It uses TREATAS to change the lineage of Opponent to Team.
Avg Diff =
VAR vTeamAvg =
MAX ( 'Table'[Team Avg] )
VAR vOpponentAvg =
CALCULATE (
MAX ( 'Table'[Team Avg] ),
ALL ( 'Table' ),
TREATAS ( VALUES ( 'Table'[Opponent] ), 'Table'[Team] )
)
VAR vResult = vTeamAvg - vOpponentAvg
RETURN
vResult
This doesn't work as my Team average is calculated as a measure and the MAX function only accepts column references
- DataInsights
Replace the column references with the measure Team Avg:
Avg Diff = VAR vTeamAvg = [Team Avg] VAR vOpponentAvg = CALCULATE ( [Team Avg], ALL ( 'Table' ), TREATAS ( VALUES ( 'Table'[Opponent] ), 'Table'[Team] ) ) VAR vResult = vTeamAvg - vOpponentAvg RETURN vResult