Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

How to rank categories?

HI, 

I have table with set order and known categories. What I want to get as result of calculated column rankColorGroup is something like this.   If you have some ideas, please share them. Thanks

  • az38's avatar
    az38
    6 years ago

    Anonymous 

    ok, let's rock

     

    first, create two calculated columns:

    _colMaxInGroup = 
VAR _nextColor = LOOKUPVALUE('Table'[color],'Table'[order],'Table'[order]+1)
RETURN
IF(_nextColor='Table'[color],0,1)

    and

    _colGroup = IF('Table'[_colMaxInGroup]=1,'Table'[order],
calculate(min('Table'[order]),FILTER(ALL('Table'),'Table'[order]>EARLIER('Table'[order]) && 'Table'[_colMaxInGroup]=1))
)

     

    and cherry on the top

    rankColourGroup = RANKX('Table','Table'[_colGroup],,,Dense)

     

    do not hesitate to give a kudo to useful posts and mark solutions as solution

    LinkedIn

     

7 Replies

  • az38's avatar
    az38
    Icon for Community Champion rankCommunity Champion
    6 years ago

    hi Anonymous 

    i couldn't find any logic in your rankColorGroup rule

    can you describe more detailed how it should be defined?

     

    do not hesitate to give a kudo to useful posts and mark solutions as solution

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Same color that form 1 or more rows in series get unique number, in ascending order.

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi Anonymous ,

     

    if I have understood it currectly your wants to rank the color by using order as Categorgy.

    To do so, please use the below DAX formula:

     

     

    RankColorGroup =
IF (
    'Table'[Color] = "Red",
    RANKX ( FILTER ( 'Table', 'Table'[Color] = "Red" ), 'Table'[Order],, ASC ),
    IF (
        'Table'[Color] = "Blue",
        RANKX ( FILTER ( 'Table', 'Table'[Color] = "Blue" ), 'Table'[Order],, ASC ),
        IF (
            'Table'[Color] = "Orange",
            RANKX ( FILTER ( 'Table', 'Table'[Color] = "Orange" ), 'Table'[Order],, ASC )
        )
    )
)

     

     

     

    If this post helps, then please consider Accepting it as the solution.

     

    Regards,

    Gaurav Raj Singh

    LinkedIN : https://www.linkedin.com/in/gauravrajsingh/