Ranks by Category

[Anonymous]

Dear All, I have a data as below and i would like to calculate the ranks based on RNO_Condition.

Data:-

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Tc07CoUwEEDRrTymlpCZ/LC0faAWlhKCYEBBYooUunsTVLC9p7jjCCiMNC74HSoQSJwpAlu9vU9L7sgFVwzFB4a4hmkrVhtG6haJWrluT792itHPRUkzTR9twrH6dGYiw58uEd0/lX++SLD2Ag==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [RNO_Condition = _t, Amt = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"RNO_Condition", type text}, {"Amt", type number}})
in
    #"Changed Type"

I have written below dax and based on that i am getting the below shown output

But i am expecting the ranking as below image.

Any help please.

Thanks,

Mohan V.

[CNENFRNL · Accepted solution]

Hi, @Anonymous , as you want to rank the entries within a sub-group, I'd suggest adding a column, whether in PQ or by calculated column, to distinguish different sub-group.

#"Extract Prefix" = Table.AddColumn(#"Changed Type", "Category", each Text.Start([RNO_Condition], 5))

or using calculated column 

The measure is

Ranks = 
VAR __cat = MAX ( 'Dataset'[Category] )
RETURN
    RANKX (
        FILTER ( ALL ( 'Dataset' ), 'Dataset'[Category] = __cat ),
        CALCULATE ( MAX ( 'Dataset'[Amt] ) )
    )

[Megha166]

Is your calculation creating groups first on the basis of the Prefix Number in RNO_Condition and then ranking on basis of Amt?

[Megha166]

@Anonymous Check this.

https://community.powerbi.com/t5/Desktop/DAX-Ranking-within-a-group/td-p/261079

Let me know if you still needs help. I will write the DAX Expression for you.