Rank by Sales with a slicer Date

[changhongchoo]

Hi,

My testing database contains three columns: Fruit, Amount, Date. How to get the ranking of the fruit by the sales and with the date slicer.

Screenshot of Data table:

Steps I tried:

1. Created a summation measure for the amount:

Sales = SUM(Data[Amount])

2. Create a calculated column for the ranking:

Ranking of Fruits = 
RANKX(
ALLSELECTED('Data'[Fruit]), [Sales]
) 

3. The result is as follow:

The expected result with the date slicer value Jan-21 will be like this:

-> The ranking is not correct, I think my ranking dax is incorrect but couldnt solve it. Appreciate any helps. Attached the pbix file link for your reference. 
Link: https://1drv.ms/u/s!AmoIew6KN0f8kDa6pjyNIjVAIkpS

[v-chenwuz-msft · Accepted solution]

Hi @changhongchoo 

Edit your code , replace  ALLSELECTED() to ALLEXCEPTE()

Ranking of Fruits =

RANKX(ALLEXCEPT('Table','Table'[Date]),[sale],,DESC)

Best Regards

Community Support Team _ chenwu zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Greg_Deckler]

@changhongchoo You may find this helpful: To *Bleep* with RANKX! - Microsoft Power BI Community