Forum Discussion
RANKX DAX function doesn't work as I expect
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
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
7 Replies
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
