Filter & Sum over multiple Tables

KickerCalcClara = 
CALCULATE(
    SUM( 'PolicyTerms'[AnnualizedOriginalPremiumTRUE] ),
    'PolicyTransactions'[TransactionType]="NBS",
    'PolicyTransactions'[EntryStamp] IN CALENDAR("2025-01-03","20205-04-01"),
    'Producers'[DisplayName]="Clara"
)

Hi millercj,

Thank you for reaching out to the Microsoft Fabric Forum Community.

It seems you're trying to apply a filter on the Producers table while also calculating a sum with specific filters on the Policy Transactions table. The problem occurs because you are attempting to directly reference Producers [DisplayName] in a CALCULATE function without establishing the necessary context or relationship between the tables. Thank you lbendlin, for your insights.

KickerCalcClara =  

CALCULATE( 

    SUM( 'PolicyTerms'[AnnualizedOriginalPremiumTRUE] ), 

    FILTER( 

        ALL('PolicyTransactions'), 

        'PolicyTransactions'[TransactionType] = "NBS" && 

        'PolicyTransactions'[EntryStamp] >= DATE(2025,1,3) && 

        'PolicyTransactions'[EntryStamp] <= DATE(2025,4,1) 

    ), 

    TREATAS( {"Clara"}, 'Producers'[DisplayName] )

)

If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.

Thank you.