RANKX DAX function doesn't work as I expect

[xuantalks]

Hi guys, 
I'm new in Power BI and DAX. I face any issue using RANKX DAX function. Could you please have a look and give me advice?

There 2 tables as below

What I'm trying to do is add 2 columns InsurerRank and InsurerGroupRank based on Total Net Earnings

I created a measure:

Total Net Earnings = SUM(Net Earnings)

I also added 2 columns into Insurers table:
InsurerRank = RANKX(ALL(Insurers), [Total Net Earnings])
InsurerGroupRank = RANKX(ALL(Insurers[Insurer Group]),[Total Net Earnings],, DESC)

But the result was not correct

 

[v-shex-msft]

Hi @xuantalks,

If you mean you want to show dynamic rank based on current filter, I'd like to suggest you use allselected to replace all function and add additional parameter 'dense' to your formula.

InsurerRank =
RANKX ( ALLSELECTED ( Insurers ), [Total Net Earnings],, DESC, DENSE )

InsurerGroupRank =
RANKX (
    ALLSELECTED ( Insurers[Insurer Group] ),
    [Total Net Earnings],
    ,
    DESC,
    DENSE
)

Regards,

Xiaoxin Sheng

[xuantalks]

@v-shex-msft InsuerGroupRank formula that you suggested returns 1 for every single Insurer Group

[v-shex-msft]

Hi @xuantalks,

Can you please share a pbix file with some sample data for test?

Regards,

Xiaoxin Sheng

[xuantalks]

hi @v-shex-msft 

Here it is data sample. Could you have a look?

Thanks a lot,

Xuan

[v-shex-msft · Accepted solution]

HI @xuantalks,

You can try to use following calculate column to replace group with other based on their ranking.

Top 5 Group =
VAR summary =
    SUMMARIZE (
        ADDCOLUMNS ( Policy_Transactions, "Group", RELATED ( Insurers[Insurer_Group] ) ),
        [Group],
        "Total", SUM ( Policy_Transactions[Net_Earnings] )
    )
VAR _rank =
    COUNTROWS (
        FILTER (
            summary,
            [Total]
                > MAXX (
                    FILTER ( summary, [Group] = EARLIER ( Insurers[Insurer_Group] ) ),
                    [Total]
                )
        )
    )
        + 1
RETURN
    IF ( _rank > 5, "Other", [Insurer_Group] )

Regards,

Xiaoxin Sheng

[xuantalks]

Thanks alot @v-shex-msft. It works

[rajulshah]

Hello @xuantalks,

Did you try using "Dense" or "Skip" property in RANKX function?