DAX question - SUMX from multiple tables

[TheJaks]

Hi all, 

Quite new to PowerbI & DAX but have done a course and making good progress. Just stuck on 1 component, where I'd like to calculate a SUMX, based on 2 tables (each of which are removed a few steps via other tables), grouped by another relate table... Unsure how to do approach this in DAX... Suspect it may need ADDCOLUMNS or nested summarize but not very familiar with that.

I have a relatively complex database, overview here:

In simple terms I'd like to show what I think is a SUMX (ie row level is important) made up of a field from the policy table (lELR) , multplied by a field in the BDX table (%_covered), grouped by the Carrier name in table Binder. 

 in SQL/Access I did it relativly easily with a query builder. Just a bit of at a loss how to in DAX!

Any tips which way to go with this would be appreciated.

[tamerj1 · Accepted solution]

Hi @TheJaks 

I would guess the following 

=
SUMX (
    SUMMARIZE (
        Payments,
        Policies[Policy ID],
        BDX[BDX],
        "@lELR", SUM ( Policies[lELR] ),
        "@covered", SUM ( BDX[%_covered] )
    ),
    [@lELR] * [@covered]
)

[TheJaks]

Abosolute legend. Worked a charm. Brilliant and thanks ever so much!!

[Jihwan_Kim]

Hi,

I think RELATED DAX function inside SUMX might help.

https://dax.guide/related/