Forum Discussion

willit6182's avatar
willit6182
Frequent Visitor
2 years ago
Solved

Summing related tables

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!!

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.