Forum Discussion

kilala's avatar
kilala
Icon for Resolver I rankResolver I
8 months ago
Solved

Rank shows all 1

Hi all,   I have a trend bar chart that want to show "Exclude top 5". It will show all months that is ranked 5 and above based on total volume.  This is the DAX I used: Top 5 Vol (Trend) = RANK...
Need Help
Show and Tell
  • danextian's avatar
    danextian
    8 months ago

    Hi kilala 

    Is DIM_TIME_PERIOD_SELECTED[Month_Year] custom sorted by another column? If so, you need to include that in your ALLSELECTED statement as well.

cengizhanarslan's avatar
cengizhanarslan
Icon for Super User rankSuper User
8 months ago

Try forcing the month context explicitly:

 
Top 5 Vol (Trend) =
VAR CurMonth = SELECTEDVALUE ( DIM_TIME_PERIOD_SELECTED[Month_Year] )
RETURN
RANKX (
    ALLSELECTED ( DIM_TIME_PERIOD_SELECTED[Month_Year] ),
    CALCULATE ( [Volume Selected], KEEPFILTERS ( DIM_TIME_PERIOD_SELECTED[Month_Year] = CurMonth ) ),
    ,
    DESC,
    Dense
)

 

Then to exclude the top 5, use this as a visual filter:

Show Month (Exclude Top 5) =
IF ( [Top 5 Vol (Trend)] > 5, 1 )

Put Show Month (Exclude Top 5) in the visual-level filters and keep only is 1.