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

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