lookup table...but how?

thanks for the trust 🙂.
I try to explain better, but I don't know what is not clear to you and what you want to modify.

T1 is the name of query that contains the table T1.

T2 is the name of the query which make the job:

First step the data of atble T2.

then merge T2 with T1

expanding ...

and finally ... averaging

Hi adavid999 ,

The workaround Anonymous  provided is great.

And you can also try to use DAX to create a calculated table.

Table =
VAR t =
    FILTER (
        CROSSJOIN (
            SELECTCOLUMNS (
                Table1,
                "No1", [Q no.],
                "Region1", [Region],
                "Town", [Town],
                "Town Score Avg", DIVIDE (
                    CALCULATE ( SUM ( Table1[Town Score] ), ALLEXCEPT ( Table1, Table1[Town] ) ),
                    CALCULATE ( COUNT ( Table1[Town] ), ALLEXCEPT ( Table1, Table1[Town] ) )
                )
            ),
            SELECTCOLUMNS (
                Table2,
                "No2", [Q no.],
                "Region2", [Region],
                "Region Score Avg", DIVIDE (
                    CALCULATE ( SUM ( Table2[Region Score] ), ALLEXCEPT ( Table2, Table2[Region] ) ),
                    CALCULATE ( COUNT ( Table2[Region] ), ALLEXCEPT ( Table2, Table2[Region] ) )
                )
            )
        ),
        [No1] = [No2]
            && [Region1] = [Region2]
    )
RETURN
    SUMMARIZE ( t, [No1], [Town], [Town Score Avg], [Region Score Avg] )

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.