How to dynamically filter rank in calculation?

[vendersonalias0]

As an example I have a sales table with some info like person, their sales amount, and the number of sales transactions. 

I created a measure to rank the number one salesperson by sales amount, which changes dynamically with filters such as region, start date, etc. The below measure works perfectly.

Sales Ranker = RANKX(ALLSELECTED('Table'),CALCULATE([Sames Amount]),,DESC,Dense)

I then wanted to create a Card visual which displays the stats of the number 1 salesperson only which also works fine and appropriately with my slicers.

Rank 1 - Sales =

CALCULATE(

[Sales Amount],

FILTER('Table', [Sales Ranker] = 1))

The issue is when I pass a filter for something like number of sales transactions > 10, the ranking breaks.

Below is the measure which does not work

Rank 1 - Sales =

CALCULATE(

[Sales Amount],
FILTER('Table'[Sales Transactions] >10),

FILTER('Table', [Sales Ranker] = 1))

Say John is the top sales person by sales amount but only has 9 sales transactions, Sam is second and has 22 sales transactions, when I use the above measure I am expecting it to pull the sales amount of Sam but instead it says (blank).

What I assume is happening, is that it calculates rank first, then it filters out anyone with sales transactions less than 10.

Is there any way to have it first filter out anyone with less than 10 transactions, then calculate rank?

Unless I am misunderstanding the reason for it breaking.

Thanks a lot!

[Anonymous · Accepted solution]

Hi @vendersonalias0 ,

You could modify the measure as:

If([sales transaction]>10,9999,[rank_sales])

Then use MINX('Table',[rank_sales]) instead of "1" in the formula.

Best Regards,

Jay

[amitchandak]

@vendersonalias0 , In second case you need a Rank with that filter too.

[vendersonalias0]

Thanks that makes sense, I'm having a bit of trouble wiht the dax, is there a way to create this rank measure while keeping the Allselected, so it changes dynamically with filter?

I created a custom column which denotes whether something has > 10 transactions, and it works well with tables as a visual filter, but I can't use it in cards since it is aggregated