Sports Leagues Standings

[az38]

Hello, powerbination!

These days, sports statistics are one of the most demanded data types around the world. Betting, fantasy leagues, ads – are the great contributors of sports stats.

Let’s look at the first (and maybe the simplest) task for all data pros who are facing this complicated industry – building the league standings. Below is my workaround for creating such kinds of reports.

Usually, there is only a list of finished matches results. It looks like this:

Team1 1-0 Team2
Team3 2-2 Team4
Team5 0-4 Team6
Team7 2-1 Team8
Team2 1-4 Team4
Team6 0-0 Team7
Team8 2-0 Team1
Team3 1-1 Team5

I’m not going to describe how to transform a list of strings to a table. Let’s imagine we succeeded with this task and get the table (‘Table1’)

Home Team	Home Score	Visitors Score	Visitors Team
Team1	1	0	Team2
Team3	2	2	Team4
Team5	0	4	Team6
Team7	2	1	Team8
Team2	1	4	Team4
Team6	0	0	Team7
Team8	2	0	Team1
Team3	1	1	Team5

Let’s add one common and simple rule: win – 3 points, draw – 1 point, loose – 0 points. If a few teams have a equal points, the next ranking is executed by Net Score margin.

After importing the data into Power BI, we have to get the sum of points by each team and information to calculate the Net Score.

Data transform

In Power Query Editor mode create new custom columns:

Home Points =
if [Home Score]>[Visitors Score] then 3
else if [Home Score]=[Visitors Score] then 1
else 0

Visitor Points =
if [Home Score]>[Visitors Score] then 0
else if [Home Score]=[Visitors Score] then 1
else 3

Home Score Allowed = [Visitors Score]

Visitors Score Allowed = [Home Score]

Also, I highly recommend creating an Index column for future analysis and troubleshooting match-by-match possibility (Add Column tab -> Index Column -> From 1).

The key magic of this technique is Unpivot columns with Team Names. Press ctrl and select two columns Home team and Visitors team, right-click Mouse and choose Unpivot columns.

Maybe you will be a little surprised, but the total row count will be doubled. And the most important – there will be two new columns: Attribute and Value:

The attribute field will be temporary for us. Meanwhile, let’s rename column Value to Team.

Add 3 more Custom columns:

Points = if [Attribute]="Home Team" then [Home Points]
else [Visitor Points]

Score = if [Attribute]="Home Team" then [Home Score]
else [Visitors Score]

Score Allowed = if [Attribute]="Home Team" then [Visitors Score]
else [Home Score]

Finish your work with Power Query Editor by removing all columns but Index, Team, Points, Score and Score Allowed. Then, do not to forget set Data Type columns Points, Score and Score Allowed as Whole Number.

Press Close & Apply.

Total M Power query code will look like this:

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCklNzDVU0lECYQMgBvGNlGJ1IDLGQBEjKAbxTeAyplD1JlAZM7iMOVS9IVTGAi4DEzXBMM0MahrMBeZwGQuoaTAZQxS3GSLZY6oUGwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Home Team" = _t, #"Home Score" = _t, #"Visitors Score" = _t, #"Visitors Team" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Home Team", type text}, {"Home Score", Int64.Type}, {"Visitors Score", Int64.Type}, {"Visitors Team", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Home Points", each if [Home Score]>[Visitors Score] then 3 else if [Home Score]=[Visitors Score] then 1 else 0),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Visitor Points", each if [Home Score]>[Visitors Score] then 0 else if [Home Score]=[Visitors Score] then 1 else 3),
#"Added Index" = Table.AddIndexColumn(#"Added Custom1", "Index", 1, 1),
#"Added Custom2" = Table.AddColumn(#"Added Index", "Home Score Allowed", each [Visitors Score]),
#"Added Custom3" = Table.AddColumn(#"Added Custom2", "Visitors Score Allowed", each [Home Score]),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Added Custom3", {"Home Score", "Visitors Score", "Home Points", "Visitor Points", "Index", "Home Score Allowed", "Visitors Score Allowed"}, "Attribute", "Value"),
#"Renamed Columns" = Table.RenameColumns(#"Unpivoted Columns",{{"Value", "Team"}}),
#"Added Custom4" = Table.AddColumn(#"Renamed Columns", "Points", each if [Attribute] = "Home Team" then [Home Points] else [Visitor Points]),
#"Added Custom5" = Table.AddColumn(#"Added Custom4", "Score", each if [Attribute]="Home Team" then [Home Score] else [Visitors Score]),
#"Added Custom6" = Table.AddColumn(#"Added Custom5", "Score Allowed", each if [Attribute]="Home Team" then [Visitors Score] else [Home Score]),
#"Removed Other Columns" = Table.SelectColumns(#"Added Custom6",{"Index", "Team", "Points", "Score", "Score Allowed"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Removed Other Columns",{{"Points", Int64.Type}, {"Score", Int64.Type}, {"Score Allowed", Int64.Type}})
in
#"Changed Type1"

Create a report

First, to sort things out, we’ll create a calculated dimension table dimTeams, that will contain all teams from the data source:

dimTeams = distinct('Table1'[Team])

Then, try to add a simple measure that will rank position of each team in our standings:

Team Position = RANKX(
ALL('dimTeams');
CALCULATE(SUM(Table1[Points]))
)

To check the result, add Table visual with ‘dimTeams'[Team Position], ‘dimTeams'[Team] and Points, Score and Score Allowed from ‘Table1’ aggregated as sum to see the extended standings:

Looks good but it’s not complete according to the rules defined above. There are a couple of ties between teams with the same points sum (Team4, Team6, Team7 are tied at 4 points; Team1 and Team8 – at 3). To resolve this case, we need to include in Measure the second rule – Net Score margin.

An interesting solution is described here https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns. We need to add multipliers, based on both rules. As long as point sum is the first rule, it should be multiplied to a big enough value. Let’s use 100,000. The second multiplier should be less, for example 10,000.

So, we get total Measure for rank teams in the standings:

Team Position = 100000*Points + 10000*Net Score

Let’s translate it to DAX:

Team Position = RANKX(
ALL('dimTeams');
CALCULATE(
SUMX(Table1;
100000*Table1[Points]+10000*(Table1[Score]-Table1[Score Allowed])
)
)
)

You could add other rules by reducing (or even increasing) this multiplier value.

As a result, our final visual will look like a real sports team-table: