topic DAX question - SUMX from multiple tables in DAX Commands and Tips

DAX question - SUMX from multiple tables

TheJaks — Sun, 30 Oct 2022 13:03:53 GMT

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.

Re: DAX question - SUMX from multiple tables

Jihwan_Kim — Sun, 30 Oct 2022 13:38:05 GMT

Hi,

I think RELATED DAX function inside SUMX might help.

https://dax.guide/related/

Re: DAX question - SUMX from multiple tables

tamerj1 — Sun, 30 Oct 2022 17:00:28 GMT

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] )

Re: DAX question - SUMX from multiple tables

TheJaks — Sun, 30 Oct 2022 18:47:05 GMT

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