cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Flotenva Frequent Visitor
Frequent Visitor

Athlete's %Performance level throughout his/her career

First of all, thanks everyone for taking some time to help.

This is the issue:

- I have a list of Track & field athletes (Name, date of birth, event, mark and date of each performance)

For example:

Athlete1 - 01.01.1990 - 100m - 10.0s - 01.01.2010

Athlete1 - 01.01.1990 - 100m - 10.5s - 06.05.2011

Athlete1 - 01.01.1990 - 100m - 10.2s - 12.10.2012

Athlete2 - xxxxxxxxxx - xxxxxx - xxxxxx - xxxxxxxxx

etc

 

The goal are:

1) to determine how old was the athlete for each individual performances.

I have done this by using DATEDIFF function (Date of birth / date of performance). No problem so far.

2) to normalize all athlete's performances for each individual so the best time/mark is 100% or career peak performance.

 

The end goal is to find out what is the performance profile for a specific sport in relation with athlete's age; so if there is any correlation, I may find out that the majority of sprinters have their best performance around the age 20-25.

Athlete's performance.png

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Athlete's %Performance level throughout his/her career

Hi @Flotenva,

 

You can try to use below formula to calculate the rank:

 

Rank = RANKX(FILTER(ALL(Table),[Athlete_Name]=EARLIER([Athlete_Name])&&[Athlete_Event]=EARLIER([Athlete_Event])),[%Performance],,DESC)

 

Capture.PNG

 

Capture2.PNG

 

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |

View solution in original post

9 REPLIES 9
vanessafvg Super Contributor
Super Contributor

Re: Athlete's %Performance level throughout his/her career

i am bit confused about what yu are needing here exactly  you say you have already got point 1.   Point  2 i can't quite make out what you asking.

 

can you give an example of your expected results?


Did I answer your question? Mark my post as a solution.
Proud a to be a Datanaut!
Anonymous
Not applicable

Re: Athlete's %Performance level throughout his/her career

Hm, so you already have his Age on specific performance. You just take the min / max (depends on the discipline), for instance the min of 100m run and save his age for his best performance. Afterwards you should have a table with 2 (or 3 columns): age, best performance and discipline. I would make a scatter plot for each seperate discipline with age and best performance. You should visual see if theres any correalation..  

Community Support Team
Community Support Team

Re: Athlete's %Performance level throughout his/her career

Hi @Flotenva,

 

According to your description, you want analysis the best performance age range, right?

If this is a case, you can take a look at below steps if it suitable for your requirement:

 

1. Add calculate column "Age" to calculate the current age based on DATEDIFF function.

2. Create new table to summary records with "Name" ,"Event" , "Age" ,average "Mark".
3. Add calculated column "Rank" to calculate rank based on average "Mark"(condition: same "athlete" and "event").

4. Create table visual with "Name", "Age" , Average "Mark", "Rank".

5. Add visual level filter to filter top n rank, you can get the best rank of each athlete.

6. Sort visual by "Age", then you can get the best performance age range.(I can't find a function to auto analysis these ranges)

 

If above not help, please share some sample data to test.

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
Flotenva Frequent Visitor
Frequent Visitor

Re: Athlete's %Performance level throughout his/her career

Thanks Xiaoxin Sheng,

I'm trying to follow your steps but I do not know how to implement the Step 3.

What function should I use? How the syntaxis should look like?

I have previous experience using functions in Excel but I'm quite new with Power BI.

Thanks for your help.

Community Support Team
Community Support Team

Re: Athlete's %Performance level throughout his/her career

Hi @Flotenva,

 

Can you please share some sample data to test?

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
Flotenva Frequent Visitor
Frequent Visitor

Re: Athlete's %Performance level throughout his/her career

Athlete_NameAthlete_DOBAthlete_CountryAthlete_EventAthlete_MarkAthlete_DatePerformance Age%PerformanceStandardizationNormalization
Andriy Kovalyov11-Jun-92UKRHigh Jump men2.2527-Jun-1523.0100%0.711.00
Andriy Kovalyov11-Jun-92UKRHigh Jump men2.220-Jun-1422.098%-0.710.00
Andriy Protsenko20-May-88UKRHigh Jump men2.2713-May-1729.095%-1.670.00
Andriy Protsenko20-May-88UKRHigh Jump men2.3316-Aug-1628.297%0.140.46
Andriy Protsenko20-May-88UKRHigh Jump men2.3207-Jul-1527.197%-0.160.38
Andriy Protsenko20-May-88UKRHigh Jump men2.325-Jun-1527.196%-0.760.23
Andriy Protsenko20-May-88UKRHigh Jump men2.403-Jul-1426.1100%2.241.00
Andriy Protsenko20-May-88UKRHigh Jump men2.3514-Jun-1426.198%0.740.62
Andriy Protsenko20-May-88UKRHigh Jump men2.3418-Jul-1426.298%0.440.54
Andriy Protsenko20-May-88UKRHigh Jump men2.3308-Jun-1426.197%0.140.46
Andriy Protsenko20-May-88UKRHigh Jump men2.3315-Aug-1426.297%0.140.46
Andriy Protsenko20-May-88UKRHigh Jump men2.3105-Sep-1426.396%-0.460.31
Andriy Protsenko20-May-88UKRHigh Jump men2.321-Jun-1426.196%-0.760.23
Flotenva Frequent Visitor
Frequent Visitor

Re: Athlete's %Performance level throughout his/her career

I would be interested on creating a column to calculate:

- % Performance

- Normalization (??)

I guess I may need to use filter context on the DAX so % performance and normalization is done for each specific athlete (name)/event's marks.

I tried your suggestion about calculating the RANK with average (condition, same Name and same Event):

Rank = CALCULATE(average('Athletics (ParseHub)'[Mark]),'Athletics (ParseHub)'[Name],'Athletics (ParseHub)'[Event])

but I think it is not right...

Community Support Team
Community Support Team

Re: Athlete's %Performance level throughout his/her career

Hi @Flotenva,

 

You can try to use below formula to calculate the rank:

 

Rank = RANKX(FILTER(ALL(Table),[Athlete_Name]=EARLIER([Athlete_Name])&&[Athlete_Event]=EARLIER([Athlete_Event])),[%Performance],,DESC)

 

Capture.PNG

 

Capture2.PNG

 

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |

View solution in original post

Flotenva Frequent Visitor
Frequent Visitor

Re: Athlete's %Performance level throughout his/her career

Thank you so much!!!

It worked perfectly

Helpful resources

Announcements
New Ranks and Rank Icons in 2020

New Ranks and Rank Icons in 2020

Read the announcement for more information!

New Kudos Given Badges Coming

New Kudos Given Badges Coming

We're rolling out new Kudos Given badges. Find out how many Kudos you've given.

November 2019 Community Highlights

November 2019 Community Highlights

Get an overview of the events and great community content from November.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)