Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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.
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.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
@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.
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.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
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?
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.
@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.
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.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
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:
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
108 | |
97 | |
79 | |
67 | |
60 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |