Forum Discussion

AuroraNI's avatar
AuroraNI
Helper III
5 years ago
Solved

difference based on dynamic rank

Hi, I have the below table in PBI and I would like another column in this table that calculates the time differences between 1st place and all the other runners, and this would change based on the e...
Need Help
  • PaulDBrown's avatar
    PaulDBrown
    5 years ago

    AuroraNI 

     

    Thanks for the file. It made things easier.

    This works:

     

V-lianl-msft's avatar
V-lianl-msft
Community Support
5 years ago

Hi AuroraNI ,

 

Create a calculated column as "dynamic rank"

rank = RANKX(FILTER('Table',EARLIER('Table'[event])='Table'[event]),'Table'[time],,ASC)

Then create a measure like this:

Measure = var _rank = SELECTEDVALUE('Table'[rank])
var _event = SELECTEDVALUE('Table'[event])
var diff = CALCULATE(MAX('Table'[time]),FILTER(ALL('Table'),'Table'[rank]=_rank-1&&'Table'[event]=_event))
var current_ = MAX('Table'[time])
return IF(ISBLANK(diff),0,current_-diff)

 

 

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • AuroraNI's avatar
    AuroraNI
    Helper III
    5 years ago

    hi V-lianl-msft thank you for taking the time to help me.  I would like the measure to calculate the time from rank =1 so the time difference gets bigger as the rank increases like below:

    Thank you for your help.