To get row number for a group in Power BI using calculated column

[jesly_ajin]

Dears,

 I have a table with many columns. One of the Columns is ProjectCBS. For each group of ProjectCBS, I need the numbering as 1,2,3.. as shown below. - RANKX(SelfService_Plan_DailyPlanQuantity,SelfService_Plan_DailyPlanQuantity[ProjectCBS],,1,Dense)

I tried this formula, but it is giving me same number for the group. I wish the output in the format shown below.

[v-frfei-msft]

Hi @jesly_ajin ,

To get the rank column in power query directly suing the code like that.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjK0NDJRitXBxTK0tCCeFQsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ProjectCBS = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ProjectCBS", Int64.Type}}),
    Partition = Table.Group(Source, {"ProjectCBS"}, {{"Partition", each Table.AddIndexColumn(_, "Index",1,1), type table}}),
    #"Expanded Partition1" = Table.ExpandTableColumn(Partition, "Partition", {"Index"}, {"Partition.Index"})
in
    #"Expanded Partition1"

[amitchandak]

Do you have any column to break the tie for project CBS

[parry2k · Accepted solution]

@jesly_ajin assuming you have a column to break the tie or add an Index column and use following expression for new column to achieve the result.

RANK = 
RANKX ( 
    FILTER ( 
        'Table',  
        'Table'[ProjectId] = EARLIER ( 'Table'[ProjectId] ) 
    ), 
    'Table'[Index], ,
    ASC, 
    Dense 
) 

[parry2k]

@jesly_ajin you need to have another column to rank within the group? Do you have any date or other column which can make each row unique if not then add Index column in Power Query and then use the Rank function.