Forum Discussion

rbustamante's avatar
rbustamante
Icon for Helper I rankHelper I
4 years ago
Solved

How to calculate row number

Hello,

I have a table with the products quantity sales. 

 

In the example I have product A that repeats 3 times and it is rank 1, product B repeats 2 times and it's rank 2 and finally product C repeats 1 one time, so it's rank 3.

 

The question it is: how to calculate the rank or row number?

 

Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    4 years ago

    Hi,

    Write these measures:

    Instances = countrows(Data)
    Rank = RANKX(ALL(Data[Product]),[Instances])

    Hope this helps.

5 Replies

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    4 years ago

    Hi,

    Write these measures:

    Instances = countrows(Data)
    Rank = RANKX(ALL(Data[Product]),[Instances])

    Hope this helps.

  • TheoC's avatar
    TheoC
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Hi rbustamante 

     

    First, create column / measure for the count of category:

     

    Measure / Column = CALCULATE ( COUNTROWS ( 'Table' ) , ALLEXCEPT ( 'Table' , Table[Product] ) )
     
    Then use a RANKX
     
    Rank = RANKX ( 'Table' , Table[Column] )
     
    If you want a single measure, you can use the following:
     
    Measure = 

    VAR _1 = CALCULATE ( COUNTROWS ( 'Table' ) , ALLEXCEPT ( 'Table' , Table[Product] ) )
    VAR _2 = RANKX ( 'Table' , 'Table'[Rank] )

    RETURN

    _2
    If you have more data that results in ties and you don't want this, just adjust the RANKX component of the column or measure to:
     
    RANKX ( 'Table' , 'Table'[Column] , , ASC , Dense )
     
    Hope this helps.
    Theo
  • TheoC's avatar
    TheoC
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Hi @rbustamante 

     

    First, create column / measure for the count of category:

     

    Measure / Column = CALCULATE ( COUNTROWS ( 'Table' ) , ALLEXCEPT ( 'Table' , Table[Product] ) )
     
    Then use a RANKX
     
    Rank = RANKX ( 'Table' , Table[Column] )
     
    If you want a single measure, you can use the following:
     
    Measure = 

    VAR _1 = CALCULATE ( COUNTROWS ( 'Table' ) , ALLEXCEPT ( 'Table' , Table[Product] ) )
    VAR _2 = RANKX ( 'Table' , 'Table'[Rank] )

    RETURN

    _2
    If you have more data that results in ties and you don't want this, just adjust the RANKX component of the column or measure to:
     
    RANKX ( 'Table' , 'Table'[Column] , , ASC , Dense )
     
    Hope this helps.
    Theo
  • TheoC's avatar
    TheoC
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Hi @rbustamante 

     

    First, create column / measure for the count of category:

     

    Measure / Column = CALCULATE ( COUNTROWS ( 'Table' ) , ALLEXCEPT ( 'Table' , Table[Product] ) )
     
    Then use a RANKX
     
    Rank = RANKX ( 'Table' , Table[Column] )
     
    If you want a single measure, you can use the following:
     
    Measure = 

    VAR _1 = CALCULATE ( COUNTROWS ( 'Table' ) , ALLEXCEPT ( 'Table' , Table[Product] ) )
    VAR _2 = RANKX ( 'Table' , 'Table'[Rank] )

    RETURN

    _2
    If you have more data that results in ties and you don't want this, just adjust the RANKX component of the column or measure to:
     
    RANKX ( 'Table' , 'Table'[Column] , , ASC , Dense )
     
    Hope this helps.
    Theo