Forum Discussion
Generating Row Number in Power BI Visualization
Hello !
I have a table displaying the top 20 company by total transaction amount as you can see :
This table can change with filter such as the date or the country.
I would like to add a column in the left of the table to display the ranking. So basicaly 1 to 20 like this :
Do you know how to do that ?
7 Replies
Try
Ranking = RANKX ( ALLSELECTED ( 'Table'[Company] ), [Tran Amount] )I've tried it but when I put table[company] i cannot choose "tran amount' as second parameter i can only pick company again
Ok so I've managed to make it work using this DAX command :
Ranking = RANKX ( ALLSELECTED ( table[company] ), CALCULATE ( SUM ( table[Tran Amount] ) ) )But as you can see the "Total" disappear for the "1" how can I use the DAX command only for the 20 first rows and not the total row ?
Ranking = IF ( ISINSCOPE ( table[company] ), RANKX ( ALLSELECTED ( table[company] ), CALCULATE ( SUM ( table[Tran Amount] ) ) ) )
Well it kinda worked but the "Total" disappeared :
Is there any way to add an "ELSE" statement to put the "Total" back ?
I think that will come back automatically if you add a column which gives a total, e.g. the sum of tran amount. Without any columns which give a meaningful total value the total row is automatically hidden.
The thing is that the row "total" is still here but the "total" id is not. So I added an else :
Ranking = IF ( ISINSCOPE ( table[company] ), RANKX ( ALLSELECTED ( table[company] ), CALCULATE ( SUM ( table[Tran Amount] ) ) ), "Total" )But as you can see the ranking is now decimal I don't know why
