Sum only on related records

[Anonymous]

Hello,

Table 1 has Position ID, Position and Location.  Table 2 has Location and NumofDoctors.  I want to find the Sum of the NumofDoctors, but only for locations that exist in Table 1.  I've used RelatedTable on Table 2 to find only the locations where they are related, and it works.  But if I sum the NumofDoctors on this new RelatedTAble, it still sums the whole Table 2 and not just those locations that match up.

[themistoklis · Accepted solution]

@Anonymous 

Try the following formula:

NumofDoctors =
VAR __locations =
    CALCULATETABLE ( VALUES ( Table2[Location] ), Table1 )
RETURN
    SUMX ( __locations, CALCULATE ( SUM ( Table2[NumofDoctors] ) ) )

See attached workspace:

[Anonymous]

Yes, that worked, thank you so much!