cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## Rankx skipping 0's/blanks

Hi - I'm having trouble with Rankx.  I don't want to rank 0's or blanks.  I was able to get the rankx to avoid assigning a rank # to 0's, however, it doesn't skip and resume to the next rank #.  For example, the -\$73k I want to be a rank of 4 instead of 2809.
Here is the formula I used to get these results.

Rank adam test \$ chg 2 = IF([\$ Chg Column for Ranking - adam]<>0, IF(ISINSCOPE((AppendedData[Brand])), RANKX(ALLSELECTED(AppendedData[Brand]),[\$ Chg Column for Ranking - adam])))

Thanks!
1 ACCEPTED SOLUTION
Super User

@trishd1223

Can you try the following measure?

``````Rank adam test \$ chg 2 =
IF (
[\$ Chg Column for Ranking - adam] <> 0
&& ISINSCOPE ( AppendedData[Brand] ),
RANKX (
FILTER (
ALLSELECTED ( AppendedData[Brand] ),
[\$ Chg Column for Ranking - adam] <> 0
),
[\$ Chg Column for Ranking - adam]
)
)``````
Did I answer your question? Mark my post as a solution! and hit thumbs up
2 REPLIES 2
Super User

@trishd1223

Can you try the following measure?

``````Rank adam test \$ chg 2 =
IF (
[\$ Chg Column for Ranking - adam] <> 0
&& ISINSCOPE ( AppendedData[Brand] ),
RANKX (
FILTER (
ALLSELECTED ( AppendedData[Brand] ),
[\$ Chg Column for Ranking - adam] <> 0
),
[\$ Chg Column for Ranking - adam]
)
)``````
Did I answer your question? Mark my post as a solution! and hit thumbs up
Super User

Try this:
Rank adam test \$ chg 2 =
IF([\$ Chg Column for Ranking - adam]<>0, IF(ISINSCOPE((AppendedData[Brand])), RANKX(FILTER(ALLSELECTED(AppendedData[Brand]) && [\$ Chg Column for Ranking - adam] <>0 && [\$ Chg Column for Ranking - adam] <>BLANK() ,[\$ Chg Column for Ranking - adam])))

Proud to be a Super User!

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

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

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors