Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

RANKX

Hello,

 

I have tried many solutions posted on this forum but with no success :(( I hope someone can help me with this case:

 

I have a data set which looks likes this: 

 

The clientnr contains unique numbers and the category level 1 and 2 are not unique and contain blank cells. I want to make a ranking based on the number of clients in category level 2 and make a table which looks like this:

 

I hope someone can post the right dax formula to solve this.

 

Thanks!!

 

Kind regards 

Tiemen

 

Need Help
  • Vvelarde's avatar
    Vvelarde
    9 years ago

    hi Anonymous

     

    Put in a matrix the columns of your table in this way:

     

     

    Then create a measure to Rank this:

     

    Ranking =
RANKX (
    ALLEXCEPT ( Table1, Table1[category level 1] ),
    CALCULATE ( SUM ( Table1[clientnr] ) ),
    ,
    DESC
)

     

     

2 Replies

  • Vvelarde's avatar
    Vvelarde
    Community Champion
    9 years ago

    hi Anonymous

     

    Put in a matrix the columns of your table in this way:

     

     

    Then create a measure to Rank this:

     

    Ranking =
RANKX (
    ALLEXCEPT ( Table1, Table1[category level 1] ),
    CALCULATE ( SUM ( Table1[clientnr] ) ),
    ,
    DESC
)

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      Thanx Vvelarde! Your solution works perfect :)