Forum Discussion
Creating an Index in Power BI - help with calculating based on current row value
I'm trying to create an Index of times, to show whether individual runners are getting faster or slower over time, with a runner's first time as 100.0 and any decrease in future times being expressed as a reduction, e.g. a 10% faster time would be expressed as 90.0, and so on.
I understand how to achieve this in a couple of ways using Excel, with some combination of MINIFS and pivot tables, but I'd like to do this in Power BI.
I thought it would be quite straightforward to create a column that shows a runners first time and then to use this to further calculate the Index value (time / first time), but I don't seem to be able to achieve this.
Here's an extract of the "data" table where the information is held:
| event_name | event_date | event_number | runner_position | runner_name | runner_gender | runner_time |
| Lyme Park parkrun | 25/02/2023 | 390 | 1 | Runner a | Male | 00:19:20 |
| Lyme Park parkrun | 25/02/2023 | 390 | 2 | Runner b | Male | 00:20:32 |
| Lyme Park parkrun | 25/02/2023 | 390 | 3 | Runner c | Male | 00:20:40 |
| Lyme Park parkrun | 25/02/2023 | 390 | 4 | Runner d | Male | 00:21:45 |
| Lyme Park parkrun | 04/03/2023 | 391 | 1 | Runner e | Male | 00:18:46 |
And here's the custom column I have tried to create, which currently only works when I manually enter a runner's name ("John Smith" in the example below):
In Excel I would simply refer to the specific cell within the current row of the "runner_name" column, but I realise this isn't the right approach in Power BI. I have searched online and tried to use the EARLIER function, but receive error messages which I have been unable to resolve.
Any help with this and/or how to produce the Index I described would be really appreciated!
7 Replies
- lbendlin
Super User
Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Please show the expected outcome based on the sample data you provided.
https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523No problem, here's a one drive link to some sample data: example_data.csv
I'd like to create:
- A column that shows the earliest date a runner appears in the data (dd/mm/yy)
- A column that shows the time that the runner achieved on that earliest date (h:mm:ss)
- An index column that shows the difference between their earliest time and their current time (decimal number)
Thanks
- lbendlin
have fun analyzing the data
