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,

[v-pagayam-msft]

Hi @kk_shp_user ,
Can you please confirm whether you have resolved issue. If yes, you are welcome to share your workaround and mark it as a solution so that other users can benefit as well. This will be helpful for other community members who have similar problems to solve it faster.
Thank you.

[v-pagayam-msft]

Hi @kk_shp_user ,
Thank you for the helpful response @johnt75 and @Elena_Kalina @!
I just wanted to kindly follow up to see if you had a chance to review the previous response provided by Community members. I hope it was helpful. If yes, please Accept the answer so that it will be helpful to others to find it quickly.
Thank you.

Regards,
Pallavi G.

[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

[kk_shp_user]

Hi @johnt75 ,

Thanks for this. The table is a huge one and so the query exceeds resources. Is there anyway to filter the data first to get only records that have [m_personal_recruits]>0 and then to rank?

I tried to filter within ALLSELECTED() but in vain.

[johnt75]

Try

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

[kk_shp_user]

Thanks, I tried this and also tried the same solution with a few different formats before your message. I can make it work for my purpose by using the rank column as a filter on Manager to filter by Bottom N.

However if I pop it in the visual, ranks for all Managers are 1.

Below is the screenshot where I have filtered by Bottom 5 (since I want ranks from 1 to 5)

[johnt75 · Accepted solution]

I think the id column is causing the issue. You'll need to add that to the ALLSELECTED, e.g.

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

You'll also need to add any other columns from the same table which are in the visual.

[v-pagayam-msft]

Hi @kk_shp_user ,
Thank you for the follow up! To help us suggest the best approach, could you please share a small sample data and the DAX for your two measures? Also, a quick note on how your visual and filters are set up would really help us understand the scenario better to provide better solution.

Regards,
Pallavi.

[Elena_Kalina]

Hi @kk_shp_user 

Perhaps this video will help you

https://www.youtube.com/watch?v=vvceE7WRcqU