Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
88 | |
63 |