Skip to main content
cancel
Showing results for 
Search instead 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.

Reply
mohitgup20
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 - 

mohitgup20_0-1697182980791.png

 

Expected Output:

 

mohitgup20_1-1697183018360.png

 

@parry2k  @amitchandak @Ritaf1983 @lbendlin 

1 ACCEPTED SOLUTION

Hi @mohitgup20 ,

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)

vcgaomsft_0-1697509845566.png

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

View solution in original post

4 REPLIES 4
mohitgup20
Frequent Visitor

Hi @v-cgao-msft , 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
New Member

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

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

@SylviaManzini 

mohitgup20_0-1697209611709.png

 

Hi @mohitgup20 ,

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)

vcgaomsft_0-1697509845566.png

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

Helpful resources

Announcements
Microsoft Fabric Learn Together

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

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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