How to calculate row number

[rbustamante]

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?

[TheoC]

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

[CNENFRNL]

Flexible solution,

Excel worksheet formula is enough

[TheoC]

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

[Ashish_Mathur · Accepted solution]

Hi,

Write these measures:

Instances = countrows(Data)

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

Hope this helps.

[TheoC]

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