Rank shows all 1

[kilala]

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]

[MichaelVenables]

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

[danextian · Accepted solution]

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]

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]

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.