cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Anonymous
Not applicable

## RANKX issue

i am trying to do a ranking by total and by months but for some reaosn when i am doing the ranking i am geting rankings for months that havent even happened please see below;

1 ACCEPTED SOLUTION
Community Support

Hi @Anonymous ,

``````Measure =
CALCULATE(
SUM(Sales[Sale 2014]),
FILTER(
ALL(Sales),
Sales[Month] = MAX(Sales[Month]) && Sales[CountryRegion] = MAX(Sales[CountryRegion])
)
)``````
``````Measure 2 =
VAR x =
RANKX(
FILTER( ALLSELECTED(Sales), Sales[Month] = MAX(Sales[Month]) ),
[Measure],
, ASC, Dense
)
VAR y =
RANKX(
FILTER( ALLSELECTED(Sales), Sales[CountryRegion] = MAX(Sales[CountryRegion]) ),
[Measure],
, ASC, Dense
)
RETURN
IF(
HASONEFILTER(Sales[CountryRegion]),
x, y
)``````

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

11 REPLIES 11
Community Support

Hi @Anonymous ,

Like this?

``````Measure =
CALCULATE(
SUM(Sales[Sale 2014]),
FILTER(
ALL(Sales),
Sales[Month] = MAX(Sales[Month])&& Sales[CountryRegion] = MAX(Sales[CountryRegion])
)
)``````
``````Measure 2 =
RANKX(
FILTER( ALLSELECTED(Sales), Sales[CountryRegion] = MAX(Sales[CountryRegion]) ),
[Measure],
, ASC, Dense
)``````

Or like this?

``````Measure 3 =
CALCULATE(
SUM(Sales[Sale 2014]),
FILTER(
ALL(Sales),
Sales[Month] = MAX(Sales[Month])
)
)``````
``````Measure 4 =
RANKX(
FILTER( ALLSELECTED(Sales),Sales[CountryRegion] = MAX(Sales[CountryRegion]) ),
[Measure 3],
, ASC, Dense
)``````

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous
Not applicable

@v-lionel-msft thank you for this, so the way i am looking to do it, is if you added your country to sit below the month on the left of matrix, you would have your ranking by month but whne you open up the month it then will give you the ranking for the countries in that month so based on the 4 countries it would rank them 1 to 4 then if i was to drill into the the country i could then get more infornation etc

Community Support

Hi @Anonymous ,

``````Measure =
CALCULATE(
SUM(Sales[Sale 2014]),
FILTER(
ALL(Sales),
Sales[Month] = MAX(Sales[Month]) && Sales[CountryRegion] = MAX(Sales[CountryRegion])
)
)``````
``````Measure 2 =
VAR x =
RANKX(
FILTER( ALLSELECTED(Sales), Sales[Month] = MAX(Sales[Month]) ),
[Measure],
, ASC, Dense
)
VAR y =
RANKX(
FILTER( ALLSELECTED(Sales), Sales[CountryRegion] = MAX(Sales[CountryRegion]) ),
[Measure],
, ASC, Dense
)
RETURN
IF(
HASONEFILTER(Sales[CountryRegion]),
x, y
)``````

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Super User

@Anonymous , You need rank for month ?

Can you share sample data and sample output in table format?

Community Champion

HI @Anonymous ,

Try measure like

Measure = IF ([Total] <> BLANK() , RANKX ......  ))

Also, if you can share sample data.

For Ranks pls refer these articles

Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

Anonymous
Not applicable

Thanks @harshnathani any idea how i could do the change of ranking by month piece?

Super User

@Anonymous , you have use isinscope and change ranking

https://www.kasperonbi.com/use-isinscope-to-get-the-right-hierarchy-level-in-dax/

example

Rank1 = Rankx(all(Table[Month]),[Sales])

Rank2 = Rankx(all(Table[Date]),[Sales])

SWITCH (
TRUE,
ISINSCOPE ( Table[Month] ), [Rank1],
ISINSCOPE ( Table[Date] ), [Rank2],
[Rank3]
)

Anonymous
Not applicable

@amitchandak so in my matrix i am trying to Rank with i have Username/Month/Region/Category then i want to rank by the total sales. But when i add my currently measure of rank Rankx(ALL(Username),[Total Sales]) it gets confused and just displays ones when i drill down from username to month then region and category. I have tried to use isinscope but can't see to get it working

Community Champion

HI @Anonymous ,

Try DateADD function to rank for last month.

Then calculate the difference.

Regards,

Harsh Nathani

Community Champion

https://www.daxpatterns.com/ranking/

Proud to be a Super User!

Memorable Member

Hi @Anonymous ,

You can try to use the below measure to do the rank:

``Measure = RANKX(filter(all(month),your_rank_measure > 0),your_rank_measure,,desc)``

If it's not what you want, please show some sample data and your data model.

Thanks.

Aiolos Zhao

Proud to be a Super User!

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

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

#### Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors