Getting Measure Value Based on Ranking

DAX DAX Calculations
1

I don’t think my topic title really describes well what I’m trying to do, but I don’t know how to say it clearly. The scenario I will describe below is simpler than my actual real-life case, but it illustrates the need I have, and protects my sensitive data.

Let’s say that I have the following table named Locations:

*Location*          *Metric*
 Location 1            12.43  
 Location 2            43.21  
 Location 3             6.76   
 Location 4             8.34  
 Location 5            65.56 
 Location 6            32.98 
 Location 7            44.44 
 Location 8             2.45  
 Location 9            59.51 
 Location 10            9.34

Now, let’s say I also have the following measures:

Location Score = (complex mathematical formula based on Locations[Metric 1] 
                  and values from two other measures)
Location Rank  = RANKX(ALL(Locations[Location]), [Location Score])

Now for my question… How can I write a measure to give me the [Location Score] value for the location that has a [Location Rank] value of 5 ?

If I have to, I can try to build a PBIX file with the fictitious data I presented above. Unfortunately, I can’t share my actual data or calculations.

Thanks in advance for your help!

2

@DaveC,

See if this does the trick:

Retrieve = 

CALCULATE(
    [Location Score],
    FILTER( 
        Data,
        [Location Rank] = 5
    )
)
  • Brian

P.S. if you want to make this dynamic, just add a parameter ranging from 1 to your max rank, and set the filter condition equal to the value of that slicer, instead of equal to 5:

Retrieve = 

CALCULATE(
    [Total Sales],
    FILTER( 
        Data,
        [Sales Rank] = [Rank Search Value]
    )
)

image
image.png749×364 14.3 KB

Full solution file:
eDNA Forum - Retrieve Value Based on Rank solution.pbix (29.1 KB)

3

Thanks! Worked perfectly!