Forum Discussion

vendersonalias0's avatar
vendersonalias0
Frequent Visitor
5 years ago

How to get date filters working in RANKX measure?

 

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.
 
 
link for pbix file attached!
 

 

Need Help

2 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    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's avatar
      vendersonalias0
      Frequent Visitor
      5 years ago

      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.