Forum Discussion
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 following table:
| Team | Opponent | Team Avg | Avg Diff |
| A | X | 1 | -9 |
| B | Y | 5 | -6 |
| C | Z | 13 | 1 |
| X | A | 10 | 9 |
| Y | B | 11 | 6 |
| Z | C | 12 | -1 |
However, I am having extreme difficulty subtracting the Team average across the columns and generating values for "Avg Diff". For Example, Team A Avg = 1 subtract Team X Avg = 10 for the Avg Diff = -9. Any help would be greatly appreciated!
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
7 Replies
Raw data file is rather large but here's the gist of it:
game team opponent pts 1 A X 1 1 B Y 2 1 C Z 3 1 X A 4 1 Y B 5 1 Z C 6 2 A Y 7 2 B Z 8 2 C X 9 2 X C 10 2 Y A 1 2 Z B 2 3 A Z 3 3 B X 4 3 C Y 5 3 X B 6 3 Y C 7 3 Z A 8 I am using a measure:
Team Avg = AVERAGE('Table'[pts])
- DataInsights
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 vResultThis 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
- parry2k
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 - Ashish_Mathur
Hi,
Not sure of what you want but try this. PBI file attached.
