Summing 2 by 2 separate columns by group by and total it up together based on country?

[JoseJondoe]

I have 4 columns in one table as shown below. I know how to sum 2 of those columns individually (home team and home goals / away team and away goals). However, after summing them up in 2 separate measures I would like to combine them based on the country to get the total value of goals per country.

I have another table with the full list of countries which I can map to.

[parry2k · Accepted solution]

@JoseJondoe Now I see the issue, first you need a seperate table for countries with the unique value from both the Home and Away teams, let's call it TeamCountry with a column called Country.

once this table is there, set the relationship of this new table with an existing table, and the relationship will be the new table country column with the home team column and away team column (two relationships), one of this relationship will be inactive, and the other one will be active.

Let's assume the relationship with the home team is active and the away team is inactive, add the following measure:

Home Team Goals = SUM ( YourTable[Home Goals] )

--this is for inactive relationship based on the assumption away team has inactive relationship
Away Team Goals = CALCULATE ( SUM ( YourTable[Away Goals] ), USERELATIONSHIP ( TeamCountry[Country], YourTable[AwayTeam Column] ) ) 

Total Goals = [Home Team Goals] + [Away Team Goals]

now in visualization, use the country column from the table and above measure, it should work.

PS - Although there is also another option (Better and easier) to unpivot your data.

✨ Follow us on LinkedIn and  to our YouTube channel

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop shop for Power BI-related projects/training/consultancy.

[JoseJondoe]

Hi,

Sorry for another message, unfortunately I notice I forgot to add one vital step which gave me the wrong result. I just notice the number of goals was overcalculated due to forgetting to group 1 more column as groupby requirement.

So the issue is I needed to groupby 2 columns (round_id and Home_team_name). The reason is because of duplicates of round_id and home team name due to multiple players within the same team and round.

Now I am back to square one where I cannot get any values at all. 

Should I do another posting or?

[JoseJondoe]

Yes, this work, thank you!

I already had a table with all distinct participated countries for a different calculation I did in SQL and I just did an inactive relationship from country to away team name and all is fine.

[parry2k]

@JoseJondoe add another measure that uses the individual measures:

Total Goals = [Home Goals Measure] + [Away Goals Measure]

in visual, use country and above measure Total Goals and that will do it.

✨ Follow us on LinkedIn and  to our YouTube channel

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop shop for Power BI-related projects/training/consultancy.

[JoseJondoe]

Hi, thank you for the response.

However, upon checking it seems my groupby measure is providing a wrong output which I do not know why.

Measure 1 =  CALCULATE(SUM('worldcupseries dashboard'[away_team_goals]), GROUPBY('worldcupseries dashboard','worldcupseries dashboard'[away_team_name]))
Measure 2 =  CALCULATE(SUM('worldcupseries dashboard'[home_team_goals]),GROUPBY('worldcupseries dashboard','worldcupseries dashboard'[home_team_name]))

Total measure = Measure 1 + Measure 2 (as stated above).

But this gives me for example france = 4196.

The total should be 4660.

I did the following to check:

Filter Measure 1 = CALCULATE(SUM('worldcupseries dashboard'[home_team_goals]),FILTER('worldcupseries dashboard','worldcupseries dashboard'[home_team_name] = "France"))

Filter Measure 2 = CALCULATE(SUM('worldcupseries dashboard'[away_team_goals]),FILTER('worldcupseries dashboard','worldcupseries dashboard'[away_team_name] = "France"))

Total Filter = Filter measure 1 + Filter measure 2