Rank Top 5 Country by Sales and not by month.

[Anonymous]

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:

@parry2k  @amitchandak @Ritaf1983 @lbendlin 

[Anonymous]

Hi @Anonymous , thanks a lot for the solution its working fine.

Can you please help me understand the reason for adding the selected value Dax condition?

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

[SylviaManzini]

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

[Anonymous]

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

@SylviaManzini 

[Anonymous · Accepted solution]

Hi @Anonymous ,

Please try like:

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