I'm sitting here watching the Olympic action in the Velodrome for men's pursuits finals and proud to say that New Zealand is currently in the top 10 teams overall in the Olympics! Well, as long as you're using the 'Gold First' ranking method that is. They're number 12 if you are ranking by total medals count.
Olympic Medal Ranking System
Lucky for New Zealand, most of the world goes off the Gold First ranking method. This is certainly how they have been ranking the teams on New Zealand TVNZ 1 throughout the Olympic games.
However, that is not always the case in the United States. I recall watching broadcasts of Olympic games where the total medal count is all that determines the order of the teams. Looking at the stats, it's not surprising that the United States does things differently (were you surprised anyway? We often march to the beat of our own drummer in the USA).
Tokyo 2020 Ranking Controversy
Do a quick Google search and you'll see some controversy around the USA media Olympic team ranking systems. Here's an excerpt from Independent.co.uk:
Historically, the difference between the Total Medal Count and Gold First Ranking has only mattered in a few years, such as 2008 when China had most Gold but USA had most Total medals.
The USA and China have been battling for first place throughout the Tokyo 2020 Olympics, with ROC and Japan high on the list as well. Currently, USA ranks first in Total Medal Count, but only second (to China) in Gold first ranking. Come on team USA - bring home the Gold!
RANKX DAX Function
I thought I'd take this opportunity to write a post on the RANKX function in DAX. This is a complex function that I have spent many hours researching, testing and tweaking. What makes the RANKX so difficult to get right?
- Dynamic measures - when using RANKX in a measure, you need to be aware of the DAX context and ensure you use the needed modifiers, such as ALL and CALCULATE, otherwise you'll end up with every team ranked number 1!
- Ties - how do you break ties in RANKX? This often becomes a math problem, and one that I need to solve for the Gold First ranking method
The DAX Measure
IF (
CALCULATE (
[Tokyo 2020 Total Medals],
ALL ( OlympicMedals )
) > 0,
CALCULATE (
RANKX (
ALL ( OlympicTeams ),
CALCULATE (
[Tokyo Gold] * 10000 + [Tokyo Silver] * 100 + [Tokyo Bronze]
),
,
DESC,
SKIP
),
ALL ( OlympicMedals )
)
)
Gold First Ranking System
Let's start by understanding the Gold First ranking system in the Olympics. It's pretty straightforward - the team with the most Gold medals is ranked 1, next most Gold medals is number 2, and so on. But what if two teams have the same number of Gold medals? Then we look at Silver for the tie break.
For example in today's rankings, France, Republic of Korea and New Zealand all have 6 Gold medals, but France have more than twice as many Silver medals with 10 Silver and are therefore ranked in 8th, higher than Republic of Korea and New Zealand. Republic of Korea and New Zealand each have 4 Silver medals, so are again tied. Therefore we look to Bronze for the tie breaker - Republic of Korea have 9 Bronze to New Zealand's 5 Bronze, therefore Republic of Korea take 9th and New Zealand take 10th place. Just 1 Gold medal and New Zealand could overtake both France and Republic of Korea to gain 8th place!
Rank Expression
In order to rank these teams accurately, I need to calculate a value or 'expression' that can be used to rank them. We need to ensure that Gold is given the most weight, Silver next and Bronze the least.
In order to make sure that a country with lots of Bronze and no Golds is not ranked above a country with 1 Gold, we need to choose the appropriate weighting. To determine this appropriate weighting, I will start with the smallest value. 1 Bronze medal gets 1 point.
Next, I need to understand my data and ask a very important question:
What is the maximum number of Bronze medals a single Team might win in a single Olympic games?
The USA have won 701 Bronze medals in total (the most of any team), so it's definitely less than that. In Rio, 359 Bronze medals were awarded to all the Teams, so again, a single team will not earn more than that. I have decided that it's highly unlikely that a single team will earn 100 Bronze medals or more in a single Olympic games. Therefore I'm granting 100 points for a Silver medal. This ensures that a Team with 1 Silver medal will always beat a team with 0 Silver medals, even if that 0 Silver Team have earned 99 Bronze medals. Okay, now how many points should a Gold medal be worth? I have chosen to give Gold medals a weighting of 10,000 points. This means that again a Team with 0 Gold medals and 99 Silver and 99 Bronze will have 99*100+99=9,999 points, but still not enough to beat a Team with 1 Gold which earns them 10,000 points.
Okay, now that we've got the mathematics out of the way, let's look at the RANKX function.
RANKX (
ALL ( OlympicTeams ),
CALCULATE (
[Tokyo Gold] * 10000 + [Tokyo Silver] * 100 + [Tokyo Bronze]
),
,
DESC,
SKIP
)
Starting from the inside, we see our weighted medal expression:
[Tokyo Gold] * 10000 + [Tokyo Silver] * 100 + [Tokyo Bronze]
)
which assigns the points we have allocated to each Gold, Silver or Bronze medal. I have put this inside a CALCULATE function for completeness and out of habit, but since we aren't using any aggregate functions it's not necessary, I just find it helpful when working with row context to always use the CALCULATE.
This expression will be evaluated over the row context of the entire list of OlympicTeams. We MUST use the ALL function here in row 2, or else every Team will be ranked as 1.
ALL ( OlympicTeams ),
Using ALL OlympicTeams ensures that we compare the current row to ALL other Teams. Without the ALL function, we'd simply be comparing New Zealand to New Zealand and France to France. That's pretty boring! We want to compare New Zealand to France, Republic of Korea, USA and ALL the OlympicTeams.
The rest of my expression helps ensure that the Rankings will display as I want them to when the user filters to show only Bronze or only Silver medals - this shouldn't change the Gold First Ranking System, so I've added the last line:
ALL ( OlympicMedals )
Go ahead and test out the report. Hopefully the USA will bring home a few more Golds so we can avoid the controversy of the two ranking systems, and I'd love to see NZ have another stellar day tomorrow and bring home some Gold to beat France and Republic of Korea.
Enjoy the Olympics!
