Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Sum only on related records

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.

1 ACCEPTED SOLUTION
themistoklis
Community Champion
Community Champion

@Anonymous 

 

Try the following formula:

 

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

 

 

See attached workspace:

 

View solution in original post

2 REPLIES 2
themistoklis
Community Champion
Community Champion

@Anonymous 

 

Try the following formula:

 

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

 

 

See attached workspace:

 

Anonymous
Not applicable

Yes, that worked, thank you so much!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.