difference based on dynamic rank

[AuroraNI]

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 event slicer.

below image is how I would like it to display:

Thanks to anyone who can provide some assistance!

[V-lianl-msft]

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]

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.

[amitchandak]

@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]

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]

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]

@AuroraNI 

You can try:

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

[AuroraNI]

Hi @PaulDBrown 

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

When I try this I get the below:

[PaulDBrown]

@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])

[AuroraNI]

Thank you @PaulDBrown 

I have tried and it seems to be working on the first row but not on the rest

[PaulDBrown]

@AuroraNI

Sorry, you need to use the "name" field in the ALLSELECTED (no the "event" field)

Also, if you are using a field from a dimension Table, you need to use the field in the ALLSELECTED from the corresponding field.

In the following example I'm ranking by sales and calculating the difference vs the smallest sales number.

The field used in the rank measure, the visual, and the [Dif vs rank 1] all come from the Dim Item table (dimension table; not the fact table) The field you use must be consistent throughout. (so either your name field is from the fact table or from the dimension table)

[AuroraNI]

hi @PaulDBrown thanks, I think they are all consistent as they are from the same table

Here is a link to the PBI file in case I am missing something.https://drive.google.com/file/d/1BKO6MVDCPCKLIJCVRd8jySxc7qV-vBRH/view?usp=sharing 

[PaulDBrown · Accepted solution]

@AuroraNI 

Thanks for the file. It made things easier.

This works:

[AuroraNI]

hi @PaulDBrown thank you very much for you help!  It is much appreciated!!