topic Re: Generating Row Number in Power BI Visualization in DAX Commands and Tips

Generating Row Number in Power BI Visualization

Wael03 — Tue, 18 Apr 2023 10:10:02 GMT

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 ?

Re: Generating Row Number in Power BI Visualization

johnt75 — Tue, 18 Apr 2023 10:28:06 GMT

Try

Ranking = RANKX ( ALLSELECTED ( 'Table'[Company] ), [Tran Amount] )

Re: Generating Row Number in Power BI Visualization

Wael03 — Tue, 18 Apr 2023 11:45:29 GMT

I've tried it but when I put table[company] i cannot choose "tran amount' as second parameter i can only pick company again

Re: Generating Row Number in Power BI Visualization

Wael03 — Tue, 18 Apr 2023 11:55:14 GMT

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 ?

Re: Generating Row Number in Power BI Visualization

johnt75 — Tue, 18 Apr 2023 12:37:01 GMT

Ranking = IF ( ISINSCOPE ( table[company] ), RANKX ( ALLSELECTED ( table[company] ), CALCULATE ( SUM ( table[Tran Amount] ) ) ) )

Re: Generating Row Number in Power BI Visualization

Wael03 — Tue, 18 Apr 2023 13:42:30 GMT

Well it kinda worked but the "Total" disappeared :

Is there any way to add an "ELSE" statement to put the "Total" back ?

Re: Generating Row Number in Power BI Visualization

johnt75 — Tue, 18 Apr 2023 13:46:06 GMT

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.

Re: Generating Row Number in Power BI Visualization

Wael03 — Tue, 18 Apr 2023 13:52:12 GMT

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