Computing Rank using 2 measures

kk_shp_user

I have a data model in which I have created two measures,
m_personal_recruits -> tells how many recruits have been hired by a manager

m_personal_recruit_qv -> tells how much sales the new recruits have done

I use measures for these metrics because they need to be dynamic for the chosen date/ month range.

How can I add a rank measure so I can filter the rank on the visual. Say - top 20, 25, etc. The ranking has to be on m_personal_recruits & m_personal_recruits_qv combined because, managers can have the same number of recruits.

I can find exmaples where multiple calculated columns are used for ranking but not where multiples measures are used,

johnt75

The RANK function allows you to specify multiple items to sort on, so you could create a measure like

Rank =
VAR BaseTable =
    ADDCOLUMNS (
        ALLSELECTED ( 'Table'[Manager] ),
        "@PersonalRecruits", [m_personal_recruits],
        "@QV", [m_personal_recruits_qv]
    )
VAR Result =
    RANK ( BaseTable, ORDERBY ( [@PersonalRecruits], DESC, [@QV], DESC ) )
RETURN
    Result