RANK / MAX sales on any given day

[kpickle]

Hi community, 

Im overthinking this one most likely.

I need to rank sales agents by both date and their sales for the day. 

If the rank is 1, they get a bonus for that day.

As im testing with the MAX and MAXX funcitons I find that I need something to filter on, which is why I believe a built in rank would be useful.

Here's what I got so far:

RANK test1 =

VAR _max = MAXX ( Sales , Sales[Sales] )

VAR _rank = RANKX ( Sales , FILTER(  _max on any given day  ) , 1 )      ..... stuck on how to create the date / _max filter

VAR _bonus = SUMX ( Bonus, Bonus[Bonus] ) 

RETURN

IF (_rank = 1, _bonus , BLANK)

[v-yueyunzh-msft · Accepted solution]

Hi , @kpickle 

Here are the steps you can refer to :

(1)This is my test data:

(2)We can click "New Column" to create a column:

test = 
VAR _RANK=RANKX( FILTER( ALL('Sheet5') , 'Sheet5'[Date] =EARLIER(Sheet5[Date])) ,CALCULATE( SUM(Sheet5[Sales]),ALLEXCEPT('Sheet5','Sheet5'[Sales])),,DESC,Dense)
return 
IF(_RANK =1 ,'Sheet5'[Bonus] , BLANK())

(3)Then we can meet your need:

If this method does not meet your needs, you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem.

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[kpickle]

Update - I was playing around within the table tyring to go the calcualted column route using the following, but Im getting circular dependency errors Im trying to work through.

MAX_CalcColumn_test1 =

VAR _maxvalue =

CALCULATE (

    MAX( Sales[Sales] ),

    ALLEXCEPT( Sales, Sales[Date] ) )

RETURN

IF( Sales = _maxvalue, "MAX" )

Using "MAX" as result just to see if it works first.

Still a work in progress.

[rsbin]

@kpickle ,

Here is a blog that provides a detailed explanation.

https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns

I believe you will find a scenario similar to yours (substitute his SubCategory with your Date )

Hope this helps.

Regards,