Ranking not consistent

[Anonymous]

Hi guys, 

I need some help to understand where my measure goes wrong. I have sales data by brand and category and my ranking measure is made on a MAT measure basis. On top you see the category and then followed by brands (via a TopN slicer I can determine how many brands I want to see and the rest is summed up by "Others") and then the next category and brands etc.

However the ranking numbering is not consistent, so sometimes it puts a number 1 behind category and a brand starts with 2,3,4 etc. And other times the category is number 1 and the first brand also nr 1.

Is there a way to fix this or is there a way to hide this measure in the visual but have it working on the background? (so the users sees the correct ranking but without the numbers)

My ranking measure:

Ranking MAT =

VAR ProductsToRank = [Waarde van TopN]

VAR Ranking =

rankx( allselected( 'Brand names'[Brand]), [MAT])

VAR IsotherSelected = SELECTEDVALUE('Brand names'[Brand]) = "Others"

VAR Result = IF(IsotherSelected, ProductsToRank +1,

IF ( Ranking <= ProductsToRank, Ranking, Ranking + 1) )

Return

 Result

[TomasAndersson · Accepted solution]

Could it be that "Others" sometimes is the largest brand and therefore "takes up a spot" when you run Ranking ? 
If so, you'd have to exclude "Others" from the table in the Ranking variable.

What visual are you using to show the ranking? For bar charts for example, you can put a measure as a tooltip and then tell the visual to sort by the tooltip value. One way to sort by rank, but not sure if it applies to you.

[TomasAndersson · Accepted solution]

Could it be that "Others" sometimes is the largest brand and therefore "takes up a spot" when you run Ranking ? 
If so, you'd have to exclude "Others" from the table in the Ranking variable.

What visual are you using to show the ranking? For bar charts for example, you can put a measure as a tooltip and then tell the visual to sort by the tooltip value. One way to sort by rank, but not sure if it applies to you.

[Anonymous]

That seems indeed to be the case, depending on the slicer "Others" can be the largest. However I want "others" to always be last in the ranking, so can I exclude others and keep this function that others keeps the last in the ranking?

I'm using a matrix which shows the data in values.

[TomasAndersson]

It should work if you adjust Ranking to

VAR Ranking =
CALCULATE(
    rankx('Brand names'[Brand], [MAT])
    FILTER(ALLSELECTED('Brand names'),'Brand names'[Brand]<>"Others")
)

"IsotherSelected" should still pick up "Others" in the result.

[Anonymous]

I had to adjust the formula a little bit, somehow it didn't work, but you've helped me a lot and it works now!

One other and last question, is there a way to hide the ranking number on category level? So now it's always category nr 1 and followed by brands which also starts with 1. Can I hide the ranking number on category level?

[TomasAndersson · Accepted solution]

Great!

I think you should be able to use the Alternate Result in SELECTEDVALUE() for that. This is probably not the most elegant solution (a lot of nested ifs), but it could hopefully help you get started.

VAR __IsCategory = SELECTEDVALUE('Brand names'[Brand],"Category") 
//If not a single Brand is selected, return "Category"

//...and then

VAR Result = 
IF(__IsCategory="Category","",  //a new if clause. Return blank if "Category", and the rest as before
     if(IsotherSelected, ProductsToRank +1,

          IF ( Ranking <= ProductsToRank, Ranking, Ranking + 1)
     )
)

[Anonymous]

Works! Thanks a lot for all your help.