cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
DSM2304
Helper II
Helper II

League Ranking Position over time

DSM2304_0-1632403346064.png

I have a Power BI report for our work Fantasy Football (Soccer, not Gridiron) league. I have various representations of the data including a league table and analysis of performance by site and department. I have one tab in the report that shows the relative performance of teams over time.

DSM2304_1-1632403539355.png

I would like to have a similar portrayal of the data but instead of the lines tracing cumulative points over the course of the season I would like to show the individual teams ranking week on week, as in the data shown in the first image above. Would that be possible and which Visualisation would be the best one to use to bring this data to life?

1 ACCEPTED SOLUTION
PaulDBrown
Super User
Super User

Assuming the data depicted is the actually weekly rank by team, first you will need to unpivot the columns for week and rank.

You can then set up your visuals with whatever preference you have. Here are a couple of options, albeit with some weaknesses.

A stepped line chart with an inverted Y axis to show rank 1 the highest (which is what you would expect for a rank)

The problem is that if there is too much data it looks pretty messy

steppedall.JPGBut it can work to compare a couple of teams:

stepped.JPG

If you want to show a larger number of teams, maybe the same line chart but shown either in a non-stepped format
Line Chart.JPG

 


or shown as small multiples. Add conditional formatting to highlight each week's number 1 and you get:

small multiples.JPG

 

I've attached the sample PBIX file

 

Edit: Just thought I'd showcase another option which has some dynamism. You can use a scatter chart with a play axis for the weeks to get this:

Scatter.gif

 

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






View solution in original post

6 REPLIES 6
PaulDBrown
Super User
Super User

Make sure the visual is sorted by Week in ascending order





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






PaulDBrown
Super User
Super User

Assuming the data depicted is the actually weekly rank by team, first you will need to unpivot the columns for week and rank.

You can then set up your visuals with whatever preference you have. Here are a couple of options, albeit with some weaknesses.

A stepped line chart with an inverted Y axis to show rank 1 the highest (which is what you would expect for a rank)

The problem is that if there is too much data it looks pretty messy

steppedall.JPGBut it can work to compare a couple of teams:

stepped.JPG

If you want to show a larger number of teams, maybe the same line chart but shown either in a non-stepped format
Line Chart.JPG

 


or shown as small multiples. Add conditional formatting to highlight each week's number 1 and you get:

small multiples.JPG

 

I've attached the sample PBIX file

 

Edit: Just thought I'd showcase another option which has some dynamism. You can use a scatter chart with a play axis for the weeks to get this:

Scatter.gif

 

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






View solution in original post

Paul - I used your solution and it is close to what I am looking to achieve. When all teams are included the Line Chart shows for the weeks in sequential order, however if I select a single player it results in the week order being subverted as per this example, this resolves if I select more than one player, any ideas why it should be doing this?

DSM2304_0-1632475903147.png

 

Paul - I am bowled over by the amount of time and effort you have put into answering my question, your suggestions look to be bang on the money and I am looking forward to implementing them. We have 65 teams in our league so I am going to include a couple of slicers to limit population by player, site, department.

Caz_16
Helper II
Helper II

@DSM2304 

As a fellow Fantasy Footballer (American football though for me), let me present my solution. 

 

First, I recommend putting your data into the following format. It will be much easier for PBI to work with your data. 

 

TeamWeekPoints

1

13

2

12
311
122
223
321
131
233
332

 

From there, you can create a "Bump Chart", I have included my example below. This is a great way to show rank. You can customize it to be fat lines with no circles, or use my example of fat circles at each week and skinny lines between. Totally up to you. Here is another good link to a website that discusses bump charts in PBI. https://www.syntelli.com/how-to-make-bump-charts-in-power-bi-tutorial 

 

Caz_16_0-1632408054927.png

 

You need 2 easy functions for this visual with the data style I showed above:

A running total for the week, which you seem to already have.

 

 

Running Points Total = 
VAR MaxWeek=
MAX('Table'[Week])
RETURN
CALCULATE(
    [Sum of Points],
    'Table'[Week] <= MaxWeek
)

 

 

 And a RANKX to calculate the rank for each team. The visual will break it up by week for you.

 

 

Running Rank = 
RANKX(
    ALLSELECTED('Table'[Team]),
    [Running Points Total]
)

 

 


Let me know if you need any further help and I hope this works for you and your fantasy league. Good luck to you!

 

-Caz

aj1973
Community Champion
Community Champion

Hi @DSM2304 

Clustered Column chart is good

aj1973_0-1632404044963.png

 

Regards
Amine Jerbi

If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!