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

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