Rankx only retuning 1s

[Anonymous]

First of all, I just want to say, I know this is a frequently asked question on the forum. I assure you I have checked over 30 forum subjects related to this problem but none of them actually solved my problem.

So thank you to whoever can help out! 🙂

I have a table called Commissions summary that are for sales reps This table generates a summary row for each sales rep for each month. I want to rank the sales rep only by each month, hence, my main filter here is by date.
-------------------------------------------------------------------
The table can be simplified down to this
Sales Reps         Date                 Target Attainment

John Deer         10/01/2019         53%

John Deer          09/01/2019        69%

Jane Doe           10/01/2019        165%

Jane Doe            09/01/2019       82%

Adam Smith      10/01/2019        74%

Adam Amith      09/01/2019        73%

---------------------------------------------------------------------
My goal is to show my table like this (let's say for October)

Sales Reps         Date                 Target Attainment        Rank

Jane Doe           10/01/2019        165%                             1

Adam Smith      10/01/2019        74%                               2

John Deer         10/01/2019         53%                               3

John Deer          09/01/2019        69%

Jane Doe            09/01/2019       82%

Adam Amith      09/01/2019        73%

---------------------------------------------------------------------
This is the Rankx formula I am using

Sales Ranks = RANKX(

FILTER(

ALLSELECTED('Commission Summary'),'Commission Summary'[Date]=10/01/2019),

CALCULATE(SUM('Commission Summary'[Target Attainment])),,DESC)

As I mentioned earlier, I am only filtering my date to October.

Please help 😞

[Ashish_Mathur]

Hi,

Parry2K's formula should work.  Just ensure that you create a Calendar Table and build a relationship from the Date column of your data table to the data column of the Calendar Table.  In the Calendar Table. create columns for Year and Month.  Build slicers from the Calendar Table for Year and Month.  Select any Year and select October.

Hope this helps.

[alena2k]

Please see if this works:

1. Add calculated column Month = FORMAT('Commission Summary''[date], "MM")

2. Measure: Sales Ranks = RANKX(ALL('Commission Summary'), CALCULATE(sum('Commission Summary'[Target Attainment]), VALUES('Commission Summary'[Month])),,DESC)

I assumed that you need to rank achievements by month.

[parry2k · Accepted solution]

@Anonymous try this measure

Rank = 
RANKX ( 
ALLSELECTED ( 'Table (2)'[Sales Reps] ), 
CALCULATE( SUM ( 'Table (2)'[Target Attainment] ) ), ,
DESC, 
Skip 
)