Syntax for Calculate, Sum, Related and AllExcept

Hi kylee_anne ,

To sum values in the Resource B table for rows where [Spreadsheet Field] equals "Budgeted Units," but preserve the filter context from a related field ([Key + Title] in the Portfolio List table), you should use ALL to remove all filters from Resource B, and VALUES to keep the filter for [Key + Title] from the related table.

You cannot use RELATED or columns from related tables inside ALLEXCEPT, so this combination achieves your goal: the measure always sums by "Budgeted Units" and only changes when the [Key + Title] from Portfolio List changes in your report.

Use this code:

PVT =
CALCULATE(
    SUM('Resource B'[Value]),
    'Resource B'[Spreadsheet Field] = "Budgeted Units",
    ALL('Resource B'),
    VALUES('Portfolio List'[Key + Title])
)

ALL('Resource B') removes all filters from the fact table. VALUES('Portfolio List'[Key + Title]) reapplies only the desired filter from your related dimension table, so your visual responds to this context as expected.