Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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 ?
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
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.
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] ) )
)
)
I've tried it but when I put table[company] i cannot choose "tran amount' as second parameter i can only pick company again
Try
Ranking =
RANKX ( ALLSELECTED ( 'Table'[Company] ), [Tran Amount] )
User | Count |
---|---|
22 | |
11 | |
8 | |
6 | |
6 |
User | Count |
---|---|
25 | |
12 | |
11 | |
8 | |
6 |