Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Flotenva
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

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
v-shex-msft
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.

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.

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.

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

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

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.

Thank you so much!!!

It worked perfectly

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

vanessafvg
Super User
Super User

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?





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.