topic Re: RANK/RANKX not working when want to see Top/Bottom values based on selection in DAX Commands and Tips

RANK/RANKX not working when want to see Top/Bottom values based on selection

singhv18 — Wed, 04 Dec 2024 11:35:13 GMT

Dear All,

I'm trying to write Rank/RankX function to give me Top5/Bottom5 values based on selection. However, this is not working for me.

Above is the output I need with the slicers it will interact with.

Problems:
I get the overall rank correct, but when I apply slicers (4 slicers on right side of image) there are 2 issues -
1. Rank starts from 2 (not 1) or,
2. Ranks for some values are skipped

All columns are from same table.

Current DAX:

# Rank =

VAR _BottomMargin = RANK(DENSE,ALLSELECTED (Partner Name, Partner Type,Partner Category),ORDERBY ( [margin], ASC))

VAR _TopMargin = RANK(DENSE,ALLSELECTED (Partner Name, Partner Type,Partner Category),ORDERBY ( [margin], DESC))

VAR _BottomRevenue = RANK(DENSE,ALLSELECTED (Partner Name, Partner Type,Partner Category),ORDERBY ( [Revenue], ASC))

VAR _TopRevenue = RANK(DENSE,ALLSELECTED (Partner Name, Partner Type,Partner Category),ORDERBY ( [Revenue], DESC))

VAR _Result = IF(SELECTEDVALUE(Selection[Selection]) = "Margin Top",_TopMargin,

IF(SELECTEDVALUE(Selection[Selection]) = "Margin Bottom", _BottomMargin,

IF(SELECTEDVALUE(Selection[Selection]) = "Revenue Top",_TopRevenue,

IF(SELECTEDVALUE(Selection[Selection]) = "Revenue Bottom", _BottomRevenue))))

RETURN

_Result

Re: RANK/RANKX not working when want to see Top/Bottom values based on selection

bhanu_gautam — Wed, 04 Dec 2024 12:17:04 GMT

singhv18 , Try using

# Rank =
VAR _SelectedValue = SELECTEDVALUE(Selection[Selection])
VAR _OrderByColumn =
SWITCH(
_SelectedValue,
"Margin Top", [margin],
"Margin Bottom", [margin],
"Revenue Top", [Revenue],
"Revenue Bottom", [Revenue]
)
VAR _Order =
SWITCH(
_SelectedValue,
"Margin Top", DESC,
"Margin Bottom", ASC,
"Revenue Top", DESC,
"Revenue Bottom", ASC
)
VAR _Rank =
RANKX(
ALLSELECTED('YourTable'),
_OrderByColumn,
,
_Order,
DENSE
)
RETURN
_Rank

Re: RANK/RANKX not working when want to see Top/Bottom values based on selection

singhv18 — Wed, 04 Dec 2024 14:55:03 GMT

bhanu_gautam

Thank you for you inputs!

Getting below error, can you pls help with this.

Re: RANK/RANKX not working when want to see Top/Bottom values based on selection

Anonymous — Mon, 09 Dec 2024 09:23:00 GMT

Thanks for the reply from bhanu_gautam, please allow me to provide another insight.
Hi singhv18 ，

Please try the following measure.

# Rank = VAR _BottomMargin = RANKX(ALLSELECTED('Table'),CALCULATE(MAX('Table'[Margin])),,DESC) VAR _TopMargin = RANKX(ALLSELECTED('Table'),CALCULATE(MAX('Table'[Margin])),,ASC) VAR _BottomRevenue = RANKX(ALLSELECTED('Table'),CALCULATE(MAX('Table'[Revenue])),,DESC) VAR _TopRevenue = RANKX(ALLSELECTED('Table'),CALCULATE(MAX('Table'[Revenue])),,ASC) RETURN SWITCH( SELECTEDVALUE(Selection[Selection]), "Margin Bottom",_BottomMargin, "Margin Top",_TopMargin, "Revenue Bottom",_BottomRevenue, "Revenue Top",_TopRevenue )

The final result is as follows.

Please see the attached pbix for reference.

Best Regards,
Dengliang Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.