Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to 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)
Regards,
Xiaoxin Sheng
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
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
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 |
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)
Regards,
Xiaoxin Sheng
Thank you so much!!!
It worked perfectly
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..
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?
Proud to be a Super User!
User | Count |
---|---|
141 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
123 | |
101 | |
71 | |
61 |