Forum Discussion

Ormy269's avatar
Ormy269
Frequent Visitor
11 months ago
Solved

Unique Values Across Two Columns Using SUMMARIZE

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.

Need Help

8 Replies

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    11 months ago

    Hi,

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

    • Ormy269's avatar
      Ormy269
      Frequent Visitor
      11 months ago

      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
  • Shahid12523's avatar
    Shahid12523
    Community Champion
    11 months ago

    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)

    • Ormy269's avatar
      Ormy269
      Frequent Visitor
      11 months ago

      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?

       

       

  • V-yubandi-msft's avatar
    V-yubandi-msft
    Community Support
    11 months ago

    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.