cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Mhrnjic
Helper I
Helper I

Ranking not consistent

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
2 ACCEPTED SOLUTIONS
TomasAndersson
Solution Specialist
Solution Specialist

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.

View solution in original post

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)
     )
)



View solution in original post

6 REPLIES 6
TomasAndersson
Solution Specialist
Solution Specialist

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.

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.

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.