Forum Discussion

AuroraNI's avatar
AuroraNI
Icon for Helper III rankHelper 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:

     

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
5 years ago

AuroraNI , Create a new column like

New colum =

var _1 = minx(filter(Table, event =earlier([event]) && [Time] > earlier([Time])), [Time])

return

if(isblank( _1),0, _1 -[Time])

 

  • AuroraNI's avatar
    AuroraNI
    Icon for Helper III rankHelper III
    5 years ago

    Hi amitchandak thank you for taking the time to repond and help me.  The column calculates the time difference from the next runner. 

     

    I would like the time difference to be calculated always from the no.1 Rank runner like below

    sorry if that was not clear in my last post

    Thank you for the help.

     

    • AuroraNI's avatar
      AuroraNI
      Icon for Helper III rankHelper III
      5 years ago

      hi amitchandak thank you again for helping, in the initial post.  Would you have time to look at my 2nd question please?  Thank you.

      Olly

      • PaulDBrown's avatar
        PaulDBrown
        Icon for Community Champion rankCommunity Champion
        5 years ago

        AuroraNI 

        You can try:

        Dif vs Rank 1 = 
VAR R1Time = CALCULATE([Time], 
              FILTER(ALLSELECTED(Table, Table [Event]), [Rank] = 1)
RETURN
[Time] - R1Time