Rank by group and order it by another related column table

[Choko_1]

Hey Community!

I have those two tables: 

1. Movie_Details - contain: "Major Genre", "Director"
2. Revenue - contain: "ROI"

   I want to get the movie with the highest ROI for each Major Genre and show the Director's name of the film.

   The tables are related by column name "Title".

   I tried to rank by group (Genre) that will be ordered descending by ROI to get all the 1's, but it didn't work.

   Where did I go wrong?

   Here's the DAX code:

Rank Genre = 
RANKX(ALLEXCEPT(Movie_Details, Movie_Details[Major Genre]),MAXX(RELATEDTABLE(Revenue),Revenue[ROI]),,DESC)

And here's an example of the ranking for the genre "Action" with descending ROI:

[daXtreme · Accepted solution]

DEFINE 

MEASURE ROIs[Total ROI] = sum(ROIs[ROI] )

MEASURE Films[Rank by ROI] = 
if ( ISINSCOPE(Films[Title] ),
    var CurrentGenre = SELECTEDVALUE( Films[Genre] )
    var RankWithinGenre =
        RANKX(
            FILTER(
                ALLSELECTED( Films ),
                Films[Genre] = CurrentGenre
            ),
            [Total ROI]
        )
    return
        RankWithinGenre
)

[Choko_1]

Thanks! it works great!