Forum Discussion
calculating values from multiple columns
Hi Guys,
I have a dataset with a list of football games.
Games List:
| Home Team | Away 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
- Ashish_Mathur
Super User
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
Community 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.- AnonymousNot 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 5for example.