Forum Discussion
Helper IIIAverage from 2 tables
Hi guys, I would like to count the average in each game depending on which players were in it. I have 2 tables connected by a one-to-many relationship. They look like this:
| ID | Player |
| 1 | A |
| 1 | B |
| 1 | C |
| 1 | D |
| 2 | D |
| 2 | C |
| 2 | E |
| 2 | F |
| 3 | A |
| 3 | G |
| 3 | B |
| 3 | C |
| 3 | D |
| 3 | E |
| Player | Score |
| A | 5 |
| B | 6 |
| C | 5,5 |
| D | 7 |
| E | 6,2 |
| F | 6,8 |
| G | 4,5 |
For example, the average for game ID[1]=(5+6+5,5+7)/4=5.875
Hi, Pan_Forex
You can try the following methods.
Column:
Score = LOOKUPVALUE(Score[Score],Score[Player],[Player])Measure:
Measure = CALCULATE(AVERAGE(Player[Score]),ALLEXCEPT(Player,Player[ID]))Measure 2 = VAR _table = CALCULATETABLE(VALUES('Player'[ID]),FILTER(ALL('Player'),'Player'[Player]=SELECTEDVALUE(Player[Player]))) RETURN CALCULATE(AVERAGE(Player[Score]),FILTER(ALL(Player),[ID] in _table))Is this the result you expect?
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
5 Replies
- Jihwan_Kim
Super User
Hi,
Please check the below picture and the attached pbix file.
Pan_ForexIt works, thank you very much. I have one more comment. What if I would like to count the average score of the players in each game but only if the player was in it. Currently if I add the player and avg score measure to the table, I get the individual average of each player, not the average of the games he participated in.
- Jihwan_Kim
Hi,
Thank you for your message.
May I ask what is the expected number that you want to see for each player?
- Pan_Forex
Sure, here we go:
Player A- 5,77
Player B- 5,77
Player C- 5,82
