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) =
RANKX(ALLSELECTED(DIM_TIME_PERIOD_SELECTED[Month_Year]),
[Volume Selected],,DESC)
 
However, this DAX ranked all month as 1. When I try to filter manually in the visual, using Top N based on [Volume Selected], I am able to get the top 5 months.
 
But I cannot use that since I need "Exclude Top 5".
 
Any idea on how to resolve this? 
 
Btw, for the chart,
X-axis: DIM_TIME_PERIOD_SELECTED[Month_Year]
Y-axis: [Volume Selected]
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.

4 Replies

  • danextian's avatar
    danextian
    Icon for Super User rankSuper User
    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.

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

      Hi danextian ,

      You are correct! This month_year column are sorted according to startperioddate column. I have included this column as well and it able to sort it correctly now.

       

      Thank you for your reply and i wish your day would go smoothly!

  • 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.

  • MichaelVenables's avatar
    MichaelVenables
    Regular Visitor
    8 months ago

    Hi Kilala,
    you might need to use the ALLSELECTED function in your measure to break the local filter context of the x axis if you try the measure below.

    Top 5 Vol (Trend) =
    RANKX(
    ALLSELECTED('DIM_TIME_PERIOD_SELECTED'[Month_Year]),
    [Volume Selected],
    ,
    DESC,
    Dense
    )

    Then when you select your bar chart. drag the new meausre into the filters on this visual section and set the filter to is greater than 5.

    Hopefully this helps.

     

    Michael