I’m trying to rank a value within a pivot table to be able to then reference it in a calculated field. I’ve been unable to use the ‘Value Field Settings’, ‘Show Value As’ steps to reference the rank and after some help from ChatGPT, it seems I need to create a measure to rank the value via Power Pivot.

I’ve added my table to the Data Model and want to try and rank a value of each horse in a race at a particular time, I’ve included a screenshot of the ranked value created in the ‘Value Field Settings’ to show you what I mean.
My table contains fields for the date, time, horse and value that I’m trying to rank but when trying to create the RANKX measure, I always seem to be either getting an error or returning incorrect information.

I’m after some help on the correct RANKX formula to be able to return the information I need?

Thanks,

Martin.Screenshot example