Rankx producing incorrect order

[Anonymous]

Hello,

I am trying below calculated column in my dataset to return a rank to limit my bar chart 

Custom Col Cancellation Ranks =

Var RankByCancellation=

RANKX(

All(MyPT_Cancellations),

CALCULATE(

COUNT(MyPT_Cancellations[No. of Clients]),

ALLEXCEPT(MyPT_Cancellations,MyPT_Cancellations[Cancellation reason])

),

,

DESC,

Dense

)

Return

IF(RankByCancellation<=9,RankByCancellation,10)

Now, this works but I doubt it does sorting counts by descending order 

Can somebody please advise me urgently?

[Anonymous]

@Anonymous 

For the first question, replace the allexcept to following:

Custom Col Cancellation Ranks =
Var RankByCancellation=
RANKX(
All(MyPT_Cancellations[Cancellation reason]), // or use allselected in place of all
CALCULATE(
COUNT(MyPT_Cancellations[No. of Clients]),
Filter(Allselected(MyPT_Cancellations), MyPT_Cancellations[Cancellation reason]=MAX( MyPT_Cancellations[Cancellation reason])
),
,
DESC,
Dense
)
Return
IF(RankByCancellation<=9,RankByCancellation,10)

For more, please share a sample table or pbix(Not image),  that would make your current situation clear for us.

Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Anonymous , are you viewing it for Cancellation reason?

Then try like

Custom Col Cancellation Ranks =
Var RankByCancellation=
RANKX(
All(MyPT_Cancellations[Cancellation reason]), // or use allselected in place of all
CALCULATE(
COUNT(MyPT_Cancellations[No. of Clients]),
ALLEXCEPT(MyPT_Cancellations,MyPT_Cancellations[Cancellation reason])
),
,
DESC,
Dense
)
Return
IF(RankByCancellation<=9,RankByCancellation,10)

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[Anonymous]

Hi @amitchandak 

Sorry, i tried Allselected but doesnt changed anything. Let me share table format and output 

[amitchandak]

@Anonymous ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[amitchandak]

@Anonymous , have tried with

All(MyPT_Cancellations[Cancellation reason])

Allselected(MyPT_Cancellations[Cancellation reason])

[Anonymous]

@amitchandak 

Tried both format but still doesnt worked 

[Anonymous]

@amitchandak 

Please guide how can remove dense rank and give separate rank if the value is same for the cancellation reason, I am doing top 19 + 20 (Other)

So, in this case rather than having 12 rank for more than 1 record I want serial rank no matter if the values are same.

I tried removing dense but the result show 1 and then 20 for all

[Anonymous]

@amitchandak @Icey  Please help