How to get date filters working in RANKX measure?

[vendersonalias0]

I have a mock table with 1000 rows of car sales data, shown above. There are only a handful of different car combinations and I am interested in seeing what the average selling price is across a time period, which is simply a measure of Total Revenue / Number of Sales

The above table is looking at the car sales breakdown minus the date. It is sorted by

Avg Sale Price = DIVIDE(SUM('Car Sales Mock'[Total Revenue]),SUM('Car Sales Mock'[Number of Sales]))

which is working well. I was also able to create a measure Avg Sale Price Rank to rank the highest priced cars on average, which works even when slicing by color, or car make.

Avg Sale Price Rank = RANKX(ALLSELECTED('Car Sales Mock'),CALCULATE([Avg Sale Price],ALLEXCEPT('Car Sales Mock','Car Sales Mock'[Car Make],'Car Sales Mock'[Car Year],'Car Sales Mock'[Color])),,DESC,Dense)

However, whenever I slice by a date, such as a month or quarter, the ranking breaks. Hence the avg sales price rank 2 and 3 measures which were my attempt at getting a working measure.

https://ilovesalt-my.sharepoint.com/:u:/p/mediadashboards/EciFcq9S6dpAhflGvkhyszUBTVlE6z-Lg-YL-iQ_XT...

link for pbix file attached!

[amitchandak]

@vendersonalias0 , Try once like this

RANKX(ALLSELECTED('Car Sales Mock','Car Sales Mock'[Car Make],'Car Sales Mock'[Car Year],'Car Sales Mock'[Color]),CALCULATE([Avg Sale Price]),,DESC,Dense)

I have not checked the file yet

[vendersonalias0]

Hi, thanks that seems to be very close, it is now working wiht the date slicer but it is skipping some numbers if the car didn't have any sales that month

Thanks, it seems liek were a step closer but there are still some weird variations such as two rank 7, and osmetimes they will skip a rank. 

I did have to adjust your measure to remove the table reference 'car sales mock' since it said that multiple argumetns are not allowed in the allselected function when the first argument is a table refrence.