Calculated Column Using Related Tables / Records

[justinharter]

I'm trying get to a total server cost for an application that may have many servers assigned to it. I have the following table structure below, but I can't seem to figure out the DAX syntax to get the calculated column I want. Essentially, I want the application record in the Application table to SUM the costs for each server that might be assigned to it (those costs are listed in the server table and are related by the "AppToServerAssignments" table).

I realize there might be a better way to do this than even using a calculated column so any insight would be greatly appreciated. In the end, I'd like to have a Card visualization that shows the applications total server annual server cost.

[v-sihou-msft]

@justinharter

In this scenario, since you also want to use a card to show total annual cost, you should create a measure in your App table to have the cost data sliced by current filter context. Just use formula: =CALCULATE(SUM(Server[Cost]))

Regards,

Simon Hou

[justinharter]

Simon,

Thanks for the response. This definitely gets me closer. I noticed your answer provides a solution if the cardinality is 1 to 1 between "AppToServerAssignments" and "Server". However, I have some servers assigned to multiple apps so I end up with the cardinality shown below in the picture. I noticed if I have this cardinality, it sums the servers for each app, but it only counts the server once (i.e. if the server is tied to another app, then it doesn't sum that). Essentially, I notice the Total Cost of Ownership for all applications that I have showing on my card visualization is the SUM of all of the servers tied to particular applications IF i removed all of the duplicate server names. Hope that makes sense.

[justinharter]

I've noticed if I make the Total a calculated column instead of a measure, then the card visualization shows the appropriate number. Not sure why this would be...