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

Athlete's performance.png

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted

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

View solution in original post

9 REPLIES 9
Highlighted
Super User I
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
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

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

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

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
Highlighted

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

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

View solution in original post

Highlighted

Thank you so much!!!

It worked perfectly

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

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