Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

calculating values from multiple columns

Hi Guys,

 

I have a dataset with a list of football games.

 

Games List:

 

Home TeamAway Team
Sweden

France

France

USA

Canada

France

Sweden

Canada

 

I have few questions about it.

 

  • I have calculated the numbers of games played by each countries with this way:
    I have Created a new table (Lis of countries) in order to have the full list of countries (Distinct & Union). Then I have created a new column.

    Games Played = CALCULATE(COUNTROWS('Games List'),FILTER('Games List','Games List'[home_team]=EARLIER('List of Countries'[Countries]))) +
    CALCULATE(COUNTROWS('Games List'),FILTER('Games List','Games List'[away_team]=EARLIER('List of Countries'[Countries])))
     
    Was it the right way to do it?
     
  • ThenI have created a new columns:
    Match = Home Team &"-"&  Away Team

 

From this column I would like to calculate few things:

  • I have calculated how many time a games happened. (i.e: Sweden - France: 3 times. France - Sweden: 6 times)
  • Then, I also would like to calculate how many time sweden played again France (either home or away)
    I would like to make appears in vizualisation like France - Sweden: 9 but Also: If you filter on Swden there is a Graph with the number of times they played a again each team

    France: 9
    Brazil 6
    Norway 5
    and so on.

 

Thank you in advance for those who will have a look. Thank you so much 🙂

 

Maxime

 

 

 

3 Replies

  • Hi,

    You should first of all Unpivot your dataset so that all teams appear in a single column and another column (with the title of Attribute) specifies the type of team.

  • V-lianl-msft's avatar
    V-lianl-msft
    Icon for Community Support rankCommunity Support

    Hi Anonymous ,

     

    Create the home and away tables as slicers,then create measures like below:

    home or away = CALCULATE(COUNTROWS(FILTER('Games List','Games List'[Home Team]=SELECTEDVALUE(Home[Home Team])&&'Games List'[Away Team]=SELECTEDVALUE(Away[Away Team]))))
    either home or away = CALCULATE(COUNTROWS(FILTER('Games List','Games List'[Home Team] in {SELECTEDVALUE(Home[Home Team]),SELECTEDVALUE(Away[Away Team])}&&'Games List'[Away Team]in {SELECTEDVALUE(Away[Away Team]),SELECTEDVALUE(Home[Home Team])})))

     

     

    Best Regards,
    Liang
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

    • Anonymous's avatar
      Anonymous
      Not applicable

      Thank you for answering but this is not what I want.

       

      How would you create a slicer (1 single one) with the list of countries and Then you get a bar chart with the top 5 of teams they play against.

       

      so you filter on sweden, then you get 

       

      France: 9
      Brazil 6
      Norway 5

       

      for example.