Forum Discussion

ajaybabuinturi's avatar
ajaybabuinturi
Super User
9 months ago
Solved

Need to Create a Rank by using Calculated Column

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...
Need Help
  • muhammad_786_1's avatar
    muhammad_786_1
    9 months ago

    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

Ashish_Mathur's avatar
Ashish_Mathur
Super User
9 months ago

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.