Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

To celebrate FabCon Vienna, we are offering 50% off select exams. Ends October 3rd. Request your discount now.

Reply
Ormy269
Frequent Visitor

Unique Values Across Two Columns Using SUMMARIZE

‎08-29-2025 04:12 PM

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:

Ormy269_0-1756508159110.png

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.

Labels:
Message 1 of 9
372 Views
0
1 ACCEPTED SOLUTION
Ashish_Mathur
Super User
Super User
In response to Ormy269

‎08-31-2025 08:30 PM

Hi,

PBI file attached.

Hope this helps.

Ashish_Mathur_0-1756697386702.png

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

Goals.pbix
Message 4 of 9
196 Views
0
8 REPLIES 8
V-yubandi-msft
Community Support
Community Support

‎08-31-2025 11:49 PM

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.

Message 9 of 9
179 Views
0
Shahid12523
Community Champion
Community Champion

‎08-30-2025 03:57 AM

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)

Shahed Shaikh
Message 7 of 9
298 Views
0
Ormy269
Frequent Visitor
In response to Shahid12523

‎08-30-2025 03:45 PM

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?

 

Ormy269_0-1756593915679.png

 

Message 8 of 9
269 Views
0
Ashish_Mathur
Super User
Super User

‎08-29-2025 07:57 PM

Hi,

Share data in a format that can be pasted in an MS Excel file and show the expected result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Message 2 of 9
328 Views
0
Ormy269
Frequent Visitor
In response to Ashish_Mathur

‎08-31-2025 06:41 AM

Hi @Ashish_Mathur

 

Below is a sample of the data I am usung. A mix of some teams playing home and away, some just one match.

 

DivDateTimeHomeTeamAwayTeamHomeAway
E207 August 20251899-12-30 20:00:00Port ValeCardiff00
E209 August 20251899-12-30 15:00:00AFC WimbledonLincoln20
E209 August 20251899-12-30 15:00:00BarnsleyBurton32
E209 August 20251899-12-30 15:00:00BoltonPlymouth20
E209 August 20251899-12-30 15:00:00ExeterBlackpool41
E209 August 20251899-12-30 15:00:00Leyton OrientWigan20
E209 August 20251899-12-30 15:00:00MansfieldDoncaster12
E209 August 20251899-12-30 15:00:00NorthamptonBradford00
E209 August 20251899-12-30 12:30:00PeterboroLuton02
E209 August 20251899-12-30 12:30:00ReadingHuddersfield02
E209 August 20251899-12-30 15:00:00StevenageRotherham10
E209 August 20251899-12-30 15:00:00WycombeStockport12
E216 August 20251899-12-30 15:00:00BarnsleyBolton11
E216 August 20251899-12-30 15:00:00BlackpoolHuddersfield32
E216 August 20251899-12-30 15:00:00BradfordLuton21
E216 August 20251899-12-30 12:30:00BurtonPort Vale00
E216 August 20251899-12-30 15:00:00CardiffRotherham30
E216 August 20251899-12-30 15:00:00DoncasterWycombe11
E216 August 20251899-12-30 12:30:00ExeterMansfield12
E216 August 20251899-12-30 15:00:00Leyton OrientStockport22
E216 August 20251899-12-30 15:00:00LincolnPlymouth32
E216 August 20251899-12-30 15:00:00ReadingAFC Wimbledon12
E216 August 20251899-12-30 15:00:00StevenageNorthampton20
E216 August 20251899-12-30 15:00:00WiganPeterboro20
E219 August 20251899-12-30 19:45:00AFC WimbledonCardiff01
E219 August 20251899-12-30 19:45:00HuddersfieldDoncaster20
E219 August 20251899-12-30 19:45:00LutonWigan10
E219 August 20251899-12-30 19:45:00MansfieldBlackpool20
E219 August 20251899-12-30 19:45:00NorthamptonLincoln01
E219 August 20251899-12-30 19:45:00PeterboroBarnsley01
E219 August 20251899-12-30 19:45:00PlymouthLeyton Orient01
E219 August 20251899-12-30 19:45:00Port ValeStevenage12
E219 August 20251899-12-30 19:45:00StockportBradford12
E219 August 20251899-12-30 19:45:00WycombeExeter01
E220 August 20251899-12-30 20:00:00BoltonReading11
E223 August 20251899-12-30 15:00:00AFC WimbledonBarnsley20
E223 August 20251899-12-30 15:00:00BoltonLincoln11
E223 August 20251899-12-30 15:00:00HuddersfieldStevenage10
E223 August 20251899-12-30 12:30:00LutonCardiff01
E223 August 20251899-12-30 15:00:00MansfieldLeyton Orient41
E223 August 20251899-12-30 15:00:00NorthamptonExeter20
E223 August 20251899-12-30 15:00:00PeterboroBradford11
E223 August 20251899-12-30 15:00:00PlymouthBlackpool10
E223 August 20251899-12-30 15:00:00Port ValeDoncaster01
E223 August 20251899-12-30 12:30:00RotherhamWigan22
E223 August 20251899-12-30 15:00:00StockportBurton21
E223 August 20251899-12-30 15:00:00WycombeReading22
G123 August 20251899-12-30 18:00:00ArisVolos NFC20
G123 August 20251899-12-30 18:00:00OlympiakosAsteras Tripolis20
G123 August 20251899-12-30 20:00:00PanetolikosAtromitos02
G124 August 20251899-12-30 18:15:00AEKPanserraikos20
G124 August 20251899-12-30 19:00:00PAOKLarisa10
G125 August 20251899-12-30 17:30:00LevadeiakosKifisia32

 

The beow is the summary table I am looking to contstruct:

 

TeamPlayedHome GoalsAway GoalsHome ConcededAway Conceded
Port Vale41030
AFC Wimbledon44211
Barnsley44132
Bolton44121
Exeter45132
Leyton Orient44224
Mansfield47231
Northampton42012
Peterboro41042
Reading41343
Stevenage43202
Wycombe43151
Blackpool43127
Bradford42312
Burton30305
Cardiff43200
Doncaster41313
Lincoln43223
Wigan42205
Huddersfield43403
Luton41312
Plymouth41215
Stockport43433
Rotherham32024
Aris12000
Olympiakos12000
Panetolikos10020
AEK12000
PAOK11000
Levadeiakos13020
Volos NFC10002
Asteras Tripolis10002
Atromitos10200
Panserraikos10002
Larisa10001
Kifisia10203
Message 3 of 9
249 Views
0
Ashish_Mathur
Super User
Super User
In response to Ormy269

‎08-31-2025 08:30 PM

Hi,

PBI file attached.

Hope this helps.

Ashish_Mathur_0-1756697386702.png

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Goals.pbix
Message 4 of 9
197 Views
0
Ormy269
Frequent Visitor
In response to Ashish_Mathur

‎09-02-2025 02:10 PM

Excellent, that seems to do the job. Thanks.

Message 5 of 9
145 Views
0
Ashish_Mathur
Super User
Super User
In response to Ormy269

‎09-02-2025 06:37 PM

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Message 6 of 9
106 Views
0

Helpful resources

Announcements
September Power BI Update Carousel

Power BI Monthly Update - September 2025

Check out the September 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

Find out what's new and trending in the Fabric community.

View All
Top Solution Authors
View All