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

[singhv18]

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

[v-denglli-msft · Accepted solution]

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.

[bhanu_gautam]

@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

[singhv18]

@bhanu_gautam 

Thank you for you inputs!

Getting below error, can you pls help with this.