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. Thank...
  • 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

     

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

  • az38's avatar
    az38
    Icon for Community Champion rankCommunity Champion
    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

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Thanks, your way of solving this gave me ideas for other things too 🙂

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

        Anonymous 

        for your second question with CaseID always the same

        _colMaxInGroup = 
VAR _nextColor = LOOKUPVALUE('Table'[color],'Table'[order],'Table'[order]+1,'Table'[CaseID],'Table'[CaseID])
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 && 'Table'[CaseID]=EARLIER('Table'[CaseID]))))

        and then

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

         

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

        LinkedIn