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?
