Order based on certain conditions

Question

I have a table with DEST values (Top N DEST) and the rest group in Other, Total Sales for those DEST and the SUM of Other. I want to create an order column that says rank the DEST based on Sales amount (largest to smallest) and put Other always in the last place.

So far I was using this formula but since other has more sales value it is given a 1.

Order = COUNTROWS(FILTER('DEST ARM Order', EARLIER('DEST ARM Order'[CWT]) <= 'DEST ARM Order'[CWT]))

Do you know how to do it?

Answer 1

Filter out "Other" in the calculation, and then add it at the bottom.

Order = 
  var result = 
    COUNTROWS(
      FILTER(
        'DEST ARM Order',
         EARLIER('DEST ARM Order'[CWT]) <= 'DEST ARM Order'[CWT] &&
         'DEST ARM Order'[Selected DEST ARM] <> "Other"
      )
    )
  return COALESCE(result, COUNTROWS('DEST ARM Order'))