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:

     

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

     

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

      Hi PaulDBrown 

      Thank you for taking the time to help me!  It is much appreciated.

      When I try this I get the below:

       

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

        AuroraNI 

        The error in [Time] is because you need to use a measure, such as:
        Time = AVERAGE(Table [Time])

        For the ALLSELECTED error, change the expression to ALLSELECTED(Table[Event])