Power BI - rankx with filter

Question

I've been struggling with this and hope someone is able to help...

I have a table with sales for products over multiple years. There is a measure that gives me the total revenue for each year by customer, ignoring product sold:

totalRevenueMeasure =
CALCULATE (
    SUM ( test[Revenue] ),
    ALLEXCEPT ( test, test[company], test[year] )
)

Year    company     Product    revenue  totalRevenueMeasure     rankx (revenue in year)
2018    company a   shoes       100             300               1
2018    company a   mugs        200             300               1
2018    Company b   shoes       250             250               2
2019    company a   lamps       300             300               2
2019    Company b   shoes       350             450               1
2019    Company b   mugs        100             450               1
2019    Company c   mugs        100             100               3
2020    company a   shoes       150             150               2
2020    Company c   lamps       200             200               1

The closest I got to the RANKX measure is below but this doesn't give the correct results. The expected output is in the RANKX column of the table above.

Customer Rank = 
          RANKX(
                ALLSELECTED( test[company],test[year]),               
                [TotalRevenueMeasure],
                , 
                DESC,
                Dense 
          )

Thanks in advance for pointers, DAX is still eluding me a bit and there might be a better way to go about it.

Following the recommendation from Alexis, success with test data but live skips some rows in rank - year 2019 doesn't have a rank #1 but has 2 rank #2. I guess this must be some kind of data issue...

Sample Data

If the data type you are using is Decimal, try to cast the result to Currency in the RANKX. sqlbi.com/blog/marco/2014/07/16/… — sergiom
This worked perfectly.. I would have been hunting hours for that.. thank you! — U01SFA3
I did actually hunt for hours for a similar issue a few days ago. — sergiom

Alexis Olson Alexis Olson · Accepted Answer · 2020-12-02T18:16:11

You're very close. The problem is that you are looking to rank each year separately but you've removed the Year filter context with your ALLSELECTED function.

Take out the second argument in ALLSELECTED so that you only have company (since you don't actually want to rank over all years for each row).

Power BI - rankx with filter

1 Answers