RANKX and specifying ASC or DESC order

[Anonymous]

Good morning everyone,

I've run into a bizarre issue that has so far stumped me and our PowerBI developers. I've built a report that is a weighted rank report. For those not familiar, it essentially contains several key metrics, as well as a "final" or overall rank based on the values of the individual metrics. The final rank is weighted, as some metrics are weighted more heavily.

The issue is that when I calculate the weighted rank, and define that I want it in ASCENDING order, some members are ranked the same when they actually have different weighted values. When I rank in DESCENING order, the problem doesn't exist, but unfortunately the values are in the opposite order that I want.

The picture below shows 18 members being ranked. Weighted Rank Value is the value that I am trying to rank. The lower the score, the better the rank. It contains the formula for weighting the 9 associated metrics (not shown in this picture).

[Greg_Deckler]

Can you post your RANKX formula?

[Anonymous]

Here's the RANKX formula I am using:

cs3122 - Final Rank = RANKX( ALLSELECTED('Performance Hierarchy'[Market]), [cs3122 - Weighted Rank])

After futher investigation, the problem is arising when I attempt to filter out certain members in the [Market] dimension. Therefore the RANKX() formula is attempting to rank all items in the list, despite the fact that I want to filter some out. We're working on a solution to that now, but thought that might help some folks who are attempting something similar.

[v-piga-msft]

Hi  @Anonymous,

Have you solved your problem? If you have solved, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

In addition, I have made a test with Rankx and get the output below.

If you still need help, please share some data sample which could reproduce your scenario, so that we can help further investigate on it? 

Best Regards,

Cherry

[Greg_Deckler]

I would expect the ALLSELECTED to handle that. However, you may have more luck creating a VAR that leverages CALCULATETABLE. CALCULATETABLE will preserve existing filters but allow you to override filters also, it works like CALCULATE but for tables. You would then use the VAR created in your RANKX function as the first argument.