Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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;            
Need Help
Show and Tell
  • v-lionel-msft's avatar
    v-lionel-msft
    6 years ago

    Hi Anonymous ,

     

    Please create measures like this.

    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.

     

Anonymous's avatar
Anonymous
Not applicable
6 years ago

HI Anonymous ,

 

Try measure like

 

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

 

Also, if you can share sample data.

 

For Ranks pls refer these articles

 

https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://databear.com/how-to-use-the-dax-rankx-function-in-power-bi/

 

 

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

 

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    HI Anonymous ,

     

     

    Try DateADD function to rank for last month.

     

    Then calculate the difference.

     

    Regards,

    Harsh Nathani

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      @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