Forum Discussion
Ranking not consistent
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.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)
)
)
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.
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.
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?
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)
)
)Works! Thanks a lot for all your help.