cancel
Showing results for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

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
Solution Sage

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.

Solution Sage

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

6 REPLIES 6
Solution Sage

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.

Helper I

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.

Solution Sage

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.

Helper I

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?

Solution Sage

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

Helper I

Works! Thanks a lot for all your help.

Announcements

#### Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

#### Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors