Join us at FabCon Atlanta from March 16 - 20, 2026, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.
Register now!To celebrate FabCon Vienna, we are offering 50% off select exams. Ends October 3rd. Request your discount now.
Hi
I have a table of football fixtures ('All Results') which contains a home team, away team and the number of goals scored by each, see below:
The full data set is much bigger than that, this is just a sample.
What I want to do is create a summary table that would list each team once, then columns for the total number of home games for each team, total away games, number of goals scored at home by that team, number of away goals scored by that team. e.g. Luton have played 1 home and away, scoring 1 at home ad 2 away. Sounds simple, but I'm at a loss as to how to do this.
I have tried to use SUMMARIZE, but I'm not sure what to enter into the GroupBy part of the function, as this needs to look across both the HomeTeam and AwayTeam columns to list every team once (some teams have so far only played at home, not away and vice versa).
I would eventually want to calculate how many they have conceded at home and away as well.
Any help is much appreciated.
Solved! Go to Solution.
Hi,
PBI file attached.
Hope this helps.
Hi @Ormy269 ,
Thank you for contacting the Fabric community.
@Ashish_Mathur , has provided a response and the PBIX file, which I have also reviewed. The results appear to match your expectations. Please review the attached PBIX file and let us know if you need any changes.
Thank you, @Ashish_Mathur , for your response.
Regards,
Yugandhar.
Team Summary =
SUMMARIZE (
UNION (
SELECTCOLUMNS ( 'All Results', "Team", [HomeTeam], "IsHome", 1, "GoalsFor", [Home], "GoalsAgainst", [Away] ),
SELECTCOLUMNS ( 'All Results', "Team", [AwayTeam], "IsHome", 0, "GoalsFor", [Away], "GoalsAgainst", [Home] )
),
[Team],
"Home Games", CALCULATE ( COUNTROWS ( 'All Results' ), [IsHome] = 1 ),
"Away Games", CALCULATE ( COUNTROWS ( 'All Results' ), [IsHome] = 0 ),
"Home Goals Scored", CALCULATE ( SUM ( [GoalsFor] ), [IsHome] = 1 ),
"Away Goals Scored", CALCULATE ( SUM ( [GoalsFor] ), [IsHome] = 0 ),
"Goals Conceded Home", CALCULATE ( SUM ( [GoalsAgainst] ), [IsHome] = 1 ),
"Goals Conceded Away", CALCULATE ( SUM ( [GoalsAgainst] ), [IsHome] = 0 )
)
This creates one summary table with:
Home/Away Games
Goals scored (home/away)
Goals conceded (home/away)
Hi @Shahid12523,
I think this is getting close, but the DAX returs some errors. Hovering the mouse over IsHome, GoalsFor and GoalsAgainst shows an error of "Cannot find Name". Also, a second error of "Parameter is not the correct type" for GoalsFor and GoalsAgainst. Does there need to be a table reference here?
Hi,
Share data in a format that can be pasted in an MS Excel file and show the expected result.
Below is a sample of the data I am usung. A mix of some teams playing home and away, some just one match.
Div | Date | Time | HomeTeam | AwayTeam | Home | Away |
E2 | 07 August 2025 | 1899-12-30 20:00:00 | Port Vale | Cardiff | 0 | 0 |
E2 | 09 August 2025 | 1899-12-30 15:00:00 | AFC Wimbledon | Lincoln | 2 | 0 |
E2 | 09 August 2025 | 1899-12-30 15:00:00 | Barnsley | Burton | 3 | 2 |
E2 | 09 August 2025 | 1899-12-30 15:00:00 | Bolton | Plymouth | 2 | 0 |
E2 | 09 August 2025 | 1899-12-30 15:00:00 | Exeter | Blackpool | 4 | 1 |
E2 | 09 August 2025 | 1899-12-30 15:00:00 | Leyton Orient | Wigan | 2 | 0 |
E2 | 09 August 2025 | 1899-12-30 15:00:00 | Mansfield | Doncaster | 1 | 2 |
E2 | 09 August 2025 | 1899-12-30 15:00:00 | Northampton | Bradford | 0 | 0 |
E2 | 09 August 2025 | 1899-12-30 12:30:00 | Peterboro | Luton | 0 | 2 |
E2 | 09 August 2025 | 1899-12-30 12:30:00 | Reading | Huddersfield | 0 | 2 |
E2 | 09 August 2025 | 1899-12-30 15:00:00 | Stevenage | Rotherham | 1 | 0 |
E2 | 09 August 2025 | 1899-12-30 15:00:00 | Wycombe | Stockport | 1 | 2 |
E2 | 16 August 2025 | 1899-12-30 15:00:00 | Barnsley | Bolton | 1 | 1 |
E2 | 16 August 2025 | 1899-12-30 15:00:00 | Blackpool | Huddersfield | 3 | 2 |
E2 | 16 August 2025 | 1899-12-30 15:00:00 | Bradford | Luton | 2 | 1 |
E2 | 16 August 2025 | 1899-12-30 12:30:00 | Burton | Port Vale | 0 | 0 |
E2 | 16 August 2025 | 1899-12-30 15:00:00 | Cardiff | Rotherham | 3 | 0 |
E2 | 16 August 2025 | 1899-12-30 15:00:00 | Doncaster | Wycombe | 1 | 1 |
E2 | 16 August 2025 | 1899-12-30 12:30:00 | Exeter | Mansfield | 1 | 2 |
E2 | 16 August 2025 | 1899-12-30 15:00:00 | Leyton Orient | Stockport | 2 | 2 |
E2 | 16 August 2025 | 1899-12-30 15:00:00 | Lincoln | Plymouth | 3 | 2 |
E2 | 16 August 2025 | 1899-12-30 15:00:00 | Reading | AFC Wimbledon | 1 | 2 |
E2 | 16 August 2025 | 1899-12-30 15:00:00 | Stevenage | Northampton | 2 | 0 |
E2 | 16 August 2025 | 1899-12-30 15:00:00 | Wigan | Peterboro | 2 | 0 |
E2 | 19 August 2025 | 1899-12-30 19:45:00 | AFC Wimbledon | Cardiff | 0 | 1 |
E2 | 19 August 2025 | 1899-12-30 19:45:00 | Huddersfield | Doncaster | 2 | 0 |
E2 | 19 August 2025 | 1899-12-30 19:45:00 | Luton | Wigan | 1 | 0 |
E2 | 19 August 2025 | 1899-12-30 19:45:00 | Mansfield | Blackpool | 2 | 0 |
E2 | 19 August 2025 | 1899-12-30 19:45:00 | Northampton | Lincoln | 0 | 1 |
E2 | 19 August 2025 | 1899-12-30 19:45:00 | Peterboro | Barnsley | 0 | 1 |
E2 | 19 August 2025 | 1899-12-30 19:45:00 | Plymouth | Leyton Orient | 0 | 1 |
E2 | 19 August 2025 | 1899-12-30 19:45:00 | Port Vale | Stevenage | 1 | 2 |
E2 | 19 August 2025 | 1899-12-30 19:45:00 | Stockport | Bradford | 1 | 2 |
E2 | 19 August 2025 | 1899-12-30 19:45:00 | Wycombe | Exeter | 0 | 1 |
E2 | 20 August 2025 | 1899-12-30 20:00:00 | Bolton | Reading | 1 | 1 |
E2 | 23 August 2025 | 1899-12-30 15:00:00 | AFC Wimbledon | Barnsley | 2 | 0 |
E2 | 23 August 2025 | 1899-12-30 15:00:00 | Bolton | Lincoln | 1 | 1 |
E2 | 23 August 2025 | 1899-12-30 15:00:00 | Huddersfield | Stevenage | 1 | 0 |
E2 | 23 August 2025 | 1899-12-30 12:30:00 | Luton | Cardiff | 0 | 1 |
E2 | 23 August 2025 | 1899-12-30 15:00:00 | Mansfield | Leyton Orient | 4 | 1 |
E2 | 23 August 2025 | 1899-12-30 15:00:00 | Northampton | Exeter | 2 | 0 |
E2 | 23 August 2025 | 1899-12-30 15:00:00 | Peterboro | Bradford | 1 | 1 |
E2 | 23 August 2025 | 1899-12-30 15:00:00 | Plymouth | Blackpool | 1 | 0 |
E2 | 23 August 2025 | 1899-12-30 15:00:00 | Port Vale | Doncaster | 0 | 1 |
E2 | 23 August 2025 | 1899-12-30 12:30:00 | Rotherham | Wigan | 2 | 2 |
E2 | 23 August 2025 | 1899-12-30 15:00:00 | Stockport | Burton | 2 | 1 |
E2 | 23 August 2025 | 1899-12-30 15:00:00 | Wycombe | Reading | 2 | 2 |
G1 | 23 August 2025 | 1899-12-30 18:00:00 | Aris | Volos NFC | 2 | 0 |
G1 | 23 August 2025 | 1899-12-30 18:00:00 | Olympiakos | Asteras Tripolis | 2 | 0 |
G1 | 23 August 2025 | 1899-12-30 20:00:00 | Panetolikos | Atromitos | 0 | 2 |
G1 | 24 August 2025 | 1899-12-30 18:15:00 | AEK | Panserraikos | 2 | 0 |
G1 | 24 August 2025 | 1899-12-30 19:00:00 | PAOK | Larisa | 1 | 0 |
G1 | 25 August 2025 | 1899-12-30 17:30:00 | Levadeiakos | Kifisia | 3 | 2 |
The beow is the summary table I am looking to contstruct:
Team | Played | Home Goals | Away Goals | Home Conceded | Away Conceded |
Port Vale | 4 | 1 | 0 | 3 | 0 |
AFC Wimbledon | 4 | 4 | 2 | 1 | 1 |
Barnsley | 4 | 4 | 1 | 3 | 2 |
Bolton | 4 | 4 | 1 | 2 | 1 |
Exeter | 4 | 5 | 1 | 3 | 2 |
Leyton Orient | 4 | 4 | 2 | 2 | 4 |
Mansfield | 4 | 7 | 2 | 3 | 1 |
Northampton | 4 | 2 | 0 | 1 | 2 |
Peterboro | 4 | 1 | 0 | 4 | 2 |
Reading | 4 | 1 | 3 | 4 | 3 |
Stevenage | 4 | 3 | 2 | 0 | 2 |
Wycombe | 4 | 3 | 1 | 5 | 1 |
Blackpool | 4 | 3 | 1 | 2 | 7 |
Bradford | 4 | 2 | 3 | 1 | 2 |
Burton | 3 | 0 | 3 | 0 | 5 |
Cardiff | 4 | 3 | 2 | 0 | 0 |
Doncaster | 4 | 1 | 3 | 1 | 3 |
Lincoln | 4 | 3 | 2 | 2 | 3 |
Wigan | 4 | 2 | 2 | 0 | 5 |
Huddersfield | 4 | 3 | 4 | 0 | 3 |
Luton | 4 | 1 | 3 | 1 | 2 |
Plymouth | 4 | 1 | 2 | 1 | 5 |
Stockport | 4 | 3 | 4 | 3 | 3 |
Rotherham | 3 | 2 | 0 | 2 | 4 |
Aris | 1 | 2 | 0 | 0 | 0 |
Olympiakos | 1 | 2 | 0 | 0 | 0 |
Panetolikos | 1 | 0 | 0 | 2 | 0 |
AEK | 1 | 2 | 0 | 0 | 0 |
PAOK | 1 | 1 | 0 | 0 | 0 |
Levadeiakos | 1 | 3 | 0 | 2 | 0 |
Volos NFC | 1 | 0 | 0 | 0 | 2 |
Asteras Tripolis | 1 | 0 | 0 | 0 | 2 |
Atromitos | 1 | 0 | 2 | 0 | 0 |
Panserraikos | 1 | 0 | 0 | 0 | 2 |
Larisa | 1 | 0 | 0 | 0 | 1 |
Kifisia | 1 | 0 | 2 | 0 | 3 |
Excellent, that seems to do the job. Thanks.
You are welcome.