cancel
Showing results for
Did you mean:
Highlighted
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.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support

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)`

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
9 REPLIES 9
Highlighted
Super User I

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 to be a Super User!

Highlighted
Anonymous
Not applicable

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..

Highlighted
Community Support

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
If this post helps, please consider accept as solution to help other members find it more quickly.
Highlighted
Frequent Visitor

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.

Highlighted
Community Support

Hi @Flotenva,

Can you please share some sample data to test?

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Highlighted
Frequent Visitor
 Athlete_Name Athlete_DOB Athlete_Country Athlete_Event Athlete_Mark Athlete_Date Performance Age %Performance Standardization Normalization Andriy Kovalyov 11-Jun-92 UKR High Jump men 2.25 27-Jun-15 23.0 100% 0.71 1.00 Andriy Kovalyov 11-Jun-92 UKR High Jump men 2.2 20-Jun-14 22.0 98% -0.71 0.00 Andriy Protsenko 20-May-88 UKR High Jump men 2.27 13-May-17 29.0 95% -1.67 0.00 Andriy Protsenko 20-May-88 UKR High Jump men 2.33 16-Aug-16 28.2 97% 0.14 0.46 Andriy Protsenko 20-May-88 UKR High Jump men 2.32 07-Jul-15 27.1 97% -0.16 0.38 Andriy Protsenko 20-May-88 UKR High Jump men 2.3 25-Jun-15 27.1 96% -0.76 0.23 Andriy Protsenko 20-May-88 UKR High Jump men 2.4 03-Jul-14 26.1 100% 2.24 1.00 Andriy Protsenko 20-May-88 UKR High Jump men 2.35 14-Jun-14 26.1 98% 0.74 0.62 Andriy Protsenko 20-May-88 UKR High Jump men 2.34 18-Jul-14 26.2 98% 0.44 0.54 Andriy Protsenko 20-May-88 UKR High Jump men 2.33 08-Jun-14 26.1 97% 0.14 0.46 Andriy Protsenko 20-May-88 UKR High Jump men 2.33 15-Aug-14 26.2 97% 0.14 0.46 Andriy Protsenko 20-May-88 UKR High Jump men 2.31 05-Sep-14 26.3 96% -0.46 0.31 Andriy Protsenko 20-May-88 UKR High Jump men 2.3 21-Jun-14 26.1 96% -0.76 0.23
Highlighted
Frequent Visitor

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...

Highlighted
Community Support

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)`

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Highlighted
Frequent Visitor

Thank you so much!!!

It worked perfectly

Announcements

#### Power Platform Community Conference

Check out the on demand sessions that are available now!

#### Microsoft Power Platform Communities

Check out the Winners!

#### Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors