topic Re: Ranking by a measure and default "Others" as the last one of the ranking in DAX Commands and Tips

Ranking by a measure and default "Others" as the last one of the ranking

Chelsea_Yu — Sun, 26 Jun 2022 15:04:29 GMT

Hello,

Your help on this will be appreciated.

There are 2 tables and I wanna creat a ranking measure that rank by "Net Sales" measure for "Group".

Net Sales = SUM(Sales) - SUM(Returns)

I want to see the Group ranking by net sales like below...

and no matter what amount of net sales does Group "Others" get, always put "Others" at the last one of the ranking.

How could I write the RANKX DAX ?

Thanks in advance.

Re: Ranking by a measure and default "Others" as the last one of the ranking

tamerj1 — Sun, 26 Jun 2022 17:43:14 GMT

Hi Chelsea_Yu

you my try

RANKX (
VALUES ( Channel[Groups] ),

IF (

Channel[Groups] = "Others",

[Net Sales]
)

)

Re: Ranking by a measure and default "Others" as the last one of the ranking

v-cazheng-msft — Wed, 29 Jun 2022 01:31:23 GMT

Hi Chelsea_Yu,

You may try this Calculated column.

Ranking = VAR rankExceptOthers = RANKX ( FILTER ( 'Channel table', 'Channel table'[Group] <> "Others" ), CALCULATE('Channel table'[Net Sales],ALLEXCEPT('Channel table','Channel table'[Group])), , DESC, DENSE ) VAR countGroup = CALCULATE ( DISTINCTCOUNT ( 'Channel table'[Group] ), ALL ( 'Channel table' ) ) RETURN IF ( 'Channel table'[Group] = "Others", countGroup, rankExceptOthers )

The result looks like this.

Also, attached the pbix file as reference.

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let me know. Thanks a lot!

Best Regards,

Community Support Team _ Caiyun

Re: Ranking by a measure and default "Others" as the last one of the ranking

Chelsea_Yu — Fri, 01 Jul 2022 11:40:03 GMT

Hi Caiyun, v-cazheng-msft

Thanks for your reply.
But I've found that it dosen't work when I want to rank the group within a certain period of time.
Please refer to the table below, the ranking is not accroding to the net sales during 5/13 - 5/31.

Your reply seems pretty close to the solution, could the way may be creating a ranking measure by temp table in dax instead of a column ?

Best Regards,
Chelsea

Re: Ranking by a measure and default "Others" as the last one of the ranking

tamerj1 — Fri, 01 Jul 2022 13:01:50 GMT

Hi Chelsea_Yu
Please use the following measure

Rank = VAR ThisGroup = SELECTEDVALUE ( 'Channel table'[Group] ) VAR Ranking = RANKX ( FILTER ( ALLSELECTED ( 'Channel table'[Group] ), 'Channel table'[Group] <> "Others" ), [Net Sales] ) RETURN IF ( ThisGroup = "Others", Ranking + 1, Ranking )

Another method:

Rank = VAR ThisGroup = SELECTEDVALUE ( 'Channel table'[Group] ) VAR SelectedGroups = ALLSELECTED ( 'Channel table'[Group] ) VAR T1 = ADDCOLUMNS ( SelectedGroups, "@Rank", RANKX ( FILTER ( ALLSELECTED ( 'Channel table'[Group] ), 'Channel table'[Group] <> "Others" ), [Net Sales] ) ) VAR CurrentRank = RANKX ( FILTER ( ALLSELECTED ( 'Channel table'[Group] ), 'Channel table'[Group] <> "Others" ), [Net Sales] ) VAR MaxRank = MAXX ( T1, [@Rank] ) RETURN IF ( ThisGroup = "Others", MaxRank + 1, CurrentRank )

Re: Ranking by a measure and default "Others" as the last one of the ranking

Chelsea_Yu — Sun, 21 Aug 2022 11:35:11 GMT

Hi tamerj1 ,

Yes this is what I need!!!

Thank you tamerj1, and sorry for the late reply. 🙂

Best Regards,

Chelsea