DAX Measure to Dynamically Rank Values and Display Where Rank = 1

[conclusnben]

Hello,

I am looking to create a DAX measure that will rank salespeople by their sum of billed amount ('Data'[Billed Amount]) within a dynamic date period ('Dates'[Year Month]). The intended result is the sum of the billed amount for the top salesperson in the period. The intended use of the dashboard will be to select a single salesperson, then select a Year Month. Those are the two necessary slicers. Also, this must be a measure because it will be used in a KPI card with other measures.

For the salesperson ranking: a salesperson cannot be ranked against all salespeople, but only their cohort of salespeople, called a Subspecialty.

For the date filtering: I have a date filter for the [Year-Month] of the date range that sets the MAX date value, and the MIN date = DATE ( YEAR ( MAX ( 'Dates'[Year Month] ) ), 1, 1 ). So far, I've been passing the MIN and MAX dates as variables inside the eventual measure. 

-

Here's my attempt at this measure, which is incorrectly giving me the summed total billed amount for salespeople, but in the correct subspecialty and correct date range:

Measure =
VAR __max =
MAX ( 'Dates'[Year Month] )
VAR __min =
DATE ( YEAR ( MAX ( 'Dates'[Year Month] ) ), 1, 1 )
VAR __calc =
CALCULATE (
CALCULATE (
SUM ( 'Data'[Billed Amount] ),
FILTER ( 'Data', FIRSTNONBLANK ( 'Data'[Subspecialty], 1 ) = [Subspecialty] )
),
FILTER ( 'Data', 'Data'[Year Month] >= __min && 'Data'[Year Month] <= __max )
)
RETURN
IF ( RANKX ( 'Data', __calc,, DESC ) < 2, __calc, BLANK () )

Does anyone have ideas on how to tweak this calculation?

[v-lionel-msft]

Hi @conclusnben ,

I'm not sure if your RANKX() formula can return the correct sorting results. Try to define this formula as a new measure separately and check whether the sorting results are correct.

Best regards,
Lionel Chen

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

[conclusnben]

Hello, how do I attach a file?

[PaulDBrown]

You can load the file into a cloud service (OneDrive, Dropbox, iCloud, Google Drive) and share from there

[PaulDBrown]

@conclusnben 

Please provide a sample dataset (dummy dataset created in Excel for example) or a sample PBIX file. Thank you.