1 Table/Matrix Visual for Calculated Columns and Data from Another Table

Hey markefrody ,

you could use a measure that is checking for the current department and then returning the value from the desired column:

Measure = 
IF( 
    HASONEVALUE('Table 1 Actual'[Department]),
    SWITCH(
        MAX( 'Table 1 Actual'[Department] ),
        "Finance", SUM( 'Table 1 Actual'[Finance Urgent Measure]),
        "HR", SUM('Table 1 Actual'[HR Non-Urgent Measure]),
        "Operations", SUM( 'Table 1 Actual'[Operations Urgent Measure] )
    )
)

However, I personally wouldn't do a calculated column.
You could also solve that in only one measure:

Sum Measure =
CALCULATE (
    SUM ( 'Table 1 Actual'[Score] ),
    KEEPFILTERS (
        ( 'Table 1 Actual'[Department] = "Finance" && 'Table 1 Actual'[Type] = "Urgent" )
        || ( 'Table 1 Actual'[Department] = "HR" && 'Table 1 Actual'[Type] = "Non-Urgent" )
        || ( 'Table 1 Actual'[Department] = "Operations" && 'Table 1 Actual'[Type] = "Urgent" )
    )
)

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards
Denis

Blog: WhatTheFact.bi
Follow me: twitter.com/DenSelimovic