Summing related tables

[willit6182]

New BI user here and I'm having trouble getting a relationship sum function to work.

I have a many-to-many relationship between ENCOUNTERID and PROCEDURE CODE. I am currently using a bridge table to create the many to many relationship. ENCOUNTERID is unique and distinct in the Encounters table. PROCEDURE CODE is unique and distinct in the CPT table.

For each ENCOUNTERID, there are many PROCEDURE CODEs and each PROCEDURE CODE has a single specific (but not unique) RVU number value assigned to it.

I am interested in the total RVU number value for a group of ENCOUNTERIDs.

I have tried creating a measure using the SUM() and SUMX() with filters but cannot seem to get it to rollup into a total for for each encounter and then add those up. I have also tried manipulating the relationship directions to no avail.

Can anyone provide any guidance?

Thanks!!

[Anonymous · Accepted solution]

Hi @willit6182

Create a Measure for RVU Total.

Total RVU = 
SUMX(
    RELATEDTABLE('BridgeTable'),
    CALCULATE(SUM('CPT'[RVU]))
)

This measure iterates over each row in the 'BridgeTable' that is related to your current context and then calculates the sum of the RVU values from the 'CPT' table.  

If you're still having problems, provide some dummy data and the desired outcome. It is best presented in the form of a table.

Regards,

Nono Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.