Need to Create a Rank by using Calculated Column

[ajaybabuinturi]

Hello,

I got a tricky requirement to create a Rank by using Calculated column based on Category and Opportunity columns. How I need to create is, create a Rank based on individually categorey(A, B & C) and skip balnks from those RANK column. I added a snippet with existing columns and expected results. 

Thanks,

Ajay 

[v-aatheeque]

Hi @ajaybabuinturi 

Have you had a chance to look through the responses shared earlier? If anything is still unclear, we’ll be happy to provide additional support.

[v-aatheeque]

Hi @ajaybabuinturi 

Just checking in to see if the previous response helped resolve your issue. If not, feel free to share your questions and we’ll be glad to assist.

[Ashish_Mathur]

Hi,

This M code works

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Category", type text}, {"Opportunity", Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type),
    #"Grouped Rows" = Table.Group(#"Added Index", {"Category"}, {{"Count", each Table.AddRankColumn(_, "Rank",    {"Opportunity", Order.Ascending},[RankKind = RankKind.Ordinal])},{"Nulls", each List.Count(_[Opportunity])-List.NonNullCount(_[Opportunity])}}),
    #"Expanded Count" = Table.ExpandTableColumn(#"Grouped Rows", "Count", {"Opportunity", "Index", "Rank"}, {"Opportunity", "Index", "Rank"}),
    #"Sorted Rows" = Table.Sort(#"Expanded Count",{{"Index", Order.Ascending}}),
    #"Added Custom" = Table.AddColumn(#"Sorted Rows", "RRank", each if [Opportunity]=null then null else [Rank]-[Nulls])[[Category],[Opportunity],[Rank]],
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Category", type text}, {"Opportunity", Int64.Type}, {"Rank", Int64.Type}})
in
    #"Changed Type1"

Hope this helps.

[muhammad_786_1 · Accepted solution]

Hello @ajaybabuinturi 

Please see, if this is what you want.

You can add an Index column using Power Query:
Go to Add Column tab → Index Column → select From 1

Then go to the Modeling tab and create a calculated column using the following DAX to get your desired output:

Rank =
IF(
NOT ISBLANK('Categories'[Opportunity]),
RANKX(
FILTER(
'Categories',
'Categories'[Category] = EARLIER('Categories'[Category])
&& NOT ISBLANK('Categories'[Opportunity])
),
'Categories'[Opportunity] + ('Categories'[Index] * 0.00001),
,
ASC,
SKIP
)
)

 

Best Regards,
Muhammad Yousaf

If this post helps, then please consider "Accept it as the solution" to help the other members find it more quickly.

LinkedIn

[ribisht17]

Did not get the logic of ranking here.