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 & C) and skip balnks from those RANK column. I added a snippet with existing columns and expected results. 



Thanks,

Ajay 

 

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

5 Replies

  • muhammad_786_1's avatar
    muhammad_786_1
    Solution Supplier
    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.

     

  • v-aatheeque's avatar
    v-aatheeque
    Community Support
    9 months ago

    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's avatar
      v-aatheeque
      Community Support
      9 months ago

      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.