Top/Bottom Rank

Power BI
1

Hi,
I’ve a problem with this measure.

Ranking =
VAR _TopBrand = RANKX(ALL(DB_MONTHLY[BRAND]),[Sales],DESC)
VAR _BottomBrand = RANKX(ALL(DB_MONTHLY[BRAND]),[Sales],ASC)
VAR _TopModel = RANKX(ALL(DB_MONTHLY[ADJ_Model]),[Sales],DESC)
VAR _BottomModel = RANKX(ALL(DB_MONTHLY[ADJ_Model]),[Sales],ASC)
VAR _TopSegment = RANKX(ALL(SEGMENTS[Total_Segments]),[Sales],DESC)
VAR _BottomSegment = RANKX(ALL(SEGMENTS[Total_Segments]),[Sales],ASC)

VAR _RANK = IF(
CONTAINSSTRING(SELECTEDVALUE(Sort_Field[Sort_Field Fields]), “Brand”)
,IF(SELECTEDVALUE(TOP_BOT[Value])= “Top” ,_TopBrand,_BottomBrand),
IF(CONTAINSSTRING(SELECTEDVALUE(Sort_Field[Sort_Field Fields]), “Model”)
,IF(SELECTEDVALUE(TOP_BOT[Value])= “Top” ,_TopModel,_BottomModel)
,IF(SELECTEDVALUE(TOP_BOT[Value])= “Top” ,_TopSegment,_BottomSegment)
)
)

RETURN
IF(_RANK<=RankN[RankN Value],[Sales])

Top/Bottom filters work well if I don’t use any FY/Month slicer, but if I want to see the bottom sales of a certain month/FY it doesn’t work anymore (it is strange that with Top option it still work)
Do you have any suggestion how to fix this problem?

image
image733×443 18.9 KB

image
image725×479 18.3 KB

image
image727×443 17.8 KB

image
image701×438 13.4 KB

2

Do you have a sample file for the forum members to have a look at, its hard to tell from just images, but if I was going to troubleshoot this, I would test the rank number that was being calculated for each line to see if it was correct, and you are getting a list of ranks when there is no filter contect.

Then I would have a look at the IF statement, it seems quite complex, for example it checks if the sort field is a string and if not defaults to Brand, then checks the selected value, if it is top returns the topbrand variable, if it doesnt it returns bottombrand, do the other items in the selection work, as reading it it seems like you would get top or bottom and nothing else.

I dont think that is the issue though, it sounds like a filter issue as you say it happens when month is selected, seeing the data model and being able to test will help everyone give you better advice, of course randomise your data before sharing for security.