Forum Discussion

Pan_Forex's avatar
Pan_Forex
Icon for Helper III rankHelper III
3 years ago
Solved

Average 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 ...
  • v-zhangti's avatar
    v-zhangti
    3 years ago

    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.

     

Pan_Forex's avatar
Pan_Forex
Icon for Helper III rankHelper III
3 years ago

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.

Jihwan_Kim's avatar
Jihwan_Kim
Icon for Super User rankSuper User
3 years ago

Hi,

Thank you for your message.

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

  • Pan_Forex's avatar
    Pan_Forex
    Icon for Helper III rankHelper III
    3 years ago

    Sure, here we go: 

    Player A- 5,77 

    Player B- 5,77

    Player C- 5,82

     

     

    • v-zhangti's avatar
      v-zhangti
      Icon for Community Support rankCommunity Support
      3 years ago

      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.