topic Average from 2 tables in DAX Commands and Tips

Average from 2 tables

Pan_Forex — Wed, 11 Jan 2023 15:40:48 GMT

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

Re: Average from 2 tables

Jihwan_Kim — Wed, 11 Jan 2023 15:51:15 GMT

Hi,

Please check the below picture and the attached pbix file.

Re: Average from 2 tables

Pan_Forex — Wed, 11 Jan 2023 16:08:52 GMT

It 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.

Re: Average from 2 tables

Jihwan_Kim — Wed, 11 Jan 2023 20:50:07 GMT

Hi,

Thank you for your message.

May I ask what is the expected number that you want to see for each player?

Re: Average from 2 tables

Pan_Forex — Wed, 11 Jan 2023 21:24:02 GMT

Sure, here we go:

Player A- 5,77

Player B- 5,77

Player C- 5,82

Re: Average from 2 tables

v-zhangti — Thu, 12 Jan 2023 06:24:41 GMT

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.