Forum Discussion
Rankx only retuning 1s
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 😞
Anonymous try this measure
Rank = RANKX ( ALLSELECTED ( 'Table (2)'[Sales Reps] ), CALCULATE( SUM ( 'Table (2)'[Target Attainment] ) ), , DESC, Skip )
3 Replies
Anonymous try this measure
Rank = RANKX ( ALLSELECTED ( 'Table (2)'[Sales Reps] ), CALCULATE( SUM ( 'Table (2)'[Target Attainment] ) ), , DESC, Skip )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.
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.
