How do I add dynamic relational values together inside a single measure without calculated columns?

Question

This is an example that I'm using to verify my formula:

director    actor   date_added  show_id
x           a       2015        Meow
x           b       2015        Meow
x           a       2020        Woof
x           c       2020        Woof
y           d       1999        Moo
y           e       2002        CaCaw
z           f       2018        Bark

The hard part is to do with the restriction of not being able to use calculated columns, tables, or modifying the original data/queries.

From this table I need to find out the longevity for each director:

director    longevity
x           6
y           4
z           1

VAR DirectorLongevityTable =
    SUMMARIZE(
        FILTER(
            'table',
            [director] <> BLANK()
            && [actor] <> BLANK()
            && [show_id] <> BLANK()
        ),
        'table'[director],
        "Longevity", MAX('table'[date_added]) - MIN('table'[date_added]) + 1
    )
 

Then find the number of movies for each pairing:

director    actor   # of movies
x           a       2
x           b       1
x           c       1
y           d       1
y           e       1
z           f       1

VAR PairedMovieCountTable =
    SUMMARIZE(
        FILTER(
            'table',
            [director] <> BLANK()
            && [actor] <> BLANK()
            && [show_id] <> BLANK()
        ),
        'table'[actor],
        "# of movies", COUNT('table'[show_id])
    )
 

So that I can have data that looks like this:

director    actor   movies  longevity
x           a       2       6
x           b       1       6
x           c       1       6
y           d       1       4
y           e       1       4
z           f       1       1

And the final calculation should be (SUM of movies/SUM of longevity) which in this case is 7 and 27 respectively for a final average of .259

I'm not sure how to get the "for each value in MOVIES, get their respective director's longevity and then add them all up to divide by the SUM of MOVIES).

I've tried a number of methods, but nothing seems to work when I add more data to the table.

EDIT: corrected table name

Answer 1

Try the following measure:

=
VAR T1 =
    SUMMARIZE (
        FILTER (
            'table',
            [director] <> BLANK ()
                && [actor] <> BLANK ()
                && [show_id] <> BLANK ()
        ),
        'table'[director],
        'table'[actor],
        "Longevity",
            CALCULATE (
                MAX ( 'table'[date_added] ) - MIN ( 'table'[date_added] ) + 1,
                ALLEXCEPT ( 'table', 'table'[director] )
            ),
        "# of movies", COUNT ( 'table'[show_id] )
    )
RETURN
    DIVIDE ( SUMX ( T1, [# of movies] ), SUMX ( T1, [Longevity] ) )