Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to 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]
)
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]
)
Abosolute legend. Worked a charm. Brilliant and thanks ever so much!!
Hi,
I think RELATED DAX function inside SUMX might help.
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
User | Count |
---|---|
57 | |
21 | |
19 | |
18 | |
16 |
User | Count |
---|---|
85 | |
80 | |
52 | |
37 | |
22 |