I'm trying to produce a matrix visualisation of previous finishes in a league. I wish to have the league seasons as columns (for example the 2017 season, 2018 season, 2019 season. The rows should be the position, so 1st place, then 2nd place etc. (down to 12th). The values should be the name of the players that have finished in the respective position on the respective league season. For example in the 2018 season Lewis Hamilton is 1st, Sebastien Vettel 2nd etc., on the 2017 season Nico Rosberg is 1st, Lewis Hamilton 2nd etc.
I'm aware I need to manipulate my data further but what I have tried has so far failed. Below is a link to my source file.
https://www.dropbox.com/s/a1rt97428he91lg/League%20Matrix.pbix?dl=0
I'll explain my data structure here:
I have a table called results (back to my Formula 1 example, this records the outcome of each race). This includes the following columns:
Player
Tournament (date format)
Result (e.g. 1st)
Points (a lookup column to a Points table where by if the 'Result' is 1st, you get 16 points)
There is a tournaments table which provides a lookup column to the Seasons table
The tournament table also identify's the "tournament type" - I would like the tournament type "Grand Final" to be excluded from this visual.
So we have the number of points every player has gained in each tournament, and know which tournaments belong to each season. I need tog et from this point, to Sum all the points each player has gained per season, rank those (to know which player was in first, second place etc.) and then use that as part of my visual.
The intended end point would look something like:
-------- 2017 -- 2018 --- (for as many seasons as I have)
1st ---- Dave -- Lee
2nd --- Lee ---- Steve
3rd --- Danny -- Laura
(down to 12th)
