Ranking a parameter on filtered grouped table

[czaldumbide]

Hi!

I'm having trouble writing a measure that ranks a parameter. Essentially, the user inputs a numeric value as a parameter and my report should output it's ranking based on grouped sales by week and client. Filters can be applied. 

To make this exercise easier I have attached a picture of my report page and a sample PBI file. The column highlighted in red is the one NOT working properly. Would appreciate any tips on how to modify it.

Since my parameter might not exist in my grouped table, I first created a measure that will return the closest value in order to then calculate the ranking. This works fine. 

Closest amount to parameter = 

var threshold = Parameter[Parameter Value] ---- returns parameter inputed by the user

VAR SourceTable =
    ADDCOLUMNS ( ALLSELECTED ( Sales[Weeknum + ClientID]), "@Amt", [Sales] ) 
    ----- temp table adding sales amount, grouped by weeknum and client 

var closest_above =
    MINX(FILTER(SourceTable, [@Amt]>= threshold), [@Amt])

var closest_below = 
    MAXX(FILTER(SourceTable, [@Amt]<= threshold), [@Amt])

var result =  if(abs(threshold-closest_above) < abs(threshold-closest_below), closest_above, closest_below)

return result

Second measure should return the rank of my parameter based on grouped table. Issue is that its not taking in account slicer filters applied to the report page. Essentially works fine until I select a filter in the slicer 'TransDesc'.

Rank of parameter = 

var SourceTable =
    ADDCOLUMNS( ALLSELECTED ( Sales[Weeknum + ClientID]), "@Amt", [Sales])

var GroupedTable = ADDCOLUMNS(FILTER(SourceTable, [@Amt] <> BLANK()), "@Rank", RANKX(SourceTable, [Sales],,ASC))

var threshold = [Closest amount to parameter]

var result = MINX(FILTER(GroupedTable, [@Amt]= threshold),  [@Rank])
return result

In example below, I would expect that Rank of parameter = 136

here is the link:
RankingSample.pbix 
Any help is appreciated!

[lbendlin · Accepted solution]

You forgot your Sales>0 filter.  Also note that you can use the [Value]  parameter of RANKX to your advantage.