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.

Frequent Visitor

## Rank Top 5 Country by Sales and not by month.

Hi,

I am trying to build a rank function by which I can achieve the top 5 countries by Sales, but when I add in the month column it shows me the top 5 countries each month whereas the expectation is to get the top5 countries and then split the value across the month to plot it on the line graph.

DAX used as of now :

Total Sale = SUMX(Orders,Orders[Sales])

Country Rank =
var _rank = RANKX(ALL(Orders[State]),[Total Sale],,DESC)
return
IF(_rank <='Number Series'[Number Series Value],1,0 )

Below is the screenshot for reference:

Current Output -

Expected Output:

1 ACCEPTED SOLUTION
Community Support

Hi @mohitgup20 ,

``````Ranking =
IF (
NOT ISBLANK ( SELECTEDVALUE ( 'Orders'[State] ) ),
RANKX (
ALL ( 'Orders'[State] ),
CALCULATE ( [Total Sales], ALL ( 'Orders'[Month] ) ),
,
DESC,
DENSE
)
)``````

The top five can then be tagged as needed:

``Measure = IF([Ranking]<=5,1,0)``

Best Regards,
Gao

Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

4 REPLIES 4
Frequent Visitor

Hi @v-cgao-msft , thanks a lot for the solution its working fine.

IF ( NOT ISBLANK ( SELECTEDVALUE ( 'Orders'[State] ) ),

New Member
`Here the solution could be to add a filter field to the RANKX ( all ( Orders [Status] )&&all([Month]), [Total Sales] , DESC )`
Frequent Visitor

Hi @sylvia,
If I try to create a measure as mentioned I am getting an error.

Community Support

Hi @mohitgup20 ,

``````Ranking =
IF (
NOT ISBLANK ( SELECTEDVALUE ( 'Orders'[State] ) ),
RANKX (
ALL ( 'Orders'[State] ),
CALCULATE ( [Total Sales], ALL ( 'Orders'[Month] ) ),
,
DESC,
DENSE
)
)``````

The top five can then be tagged as needed:

``Measure = IF([Ranking]<=5,1,0)``

Best Regards,
Gao

Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

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