Sum data from one table based on information from a second table linked through intermediate table

Hi Samboko,

Create measures like below:

Measure1 =
CONCATENATEX (
    ALLSELECTED ( 'Audit Database' ),
    'Audit Database'[Location],
    ","
)

Measure2 =
IF (
    ISERROR (
        FIND ( SELECTEDVALUE ( 'Collection Database'[Location] ), [Measure1] )
    ),
    "Not Exist",
    "Exist"
)

Measure3 =
CALCULATE (
    SUM ( 'Collection Database'[Collected Tonnes] ),
    FILTER ( 'Collection Database', [Measure2] = "Exist" )
)

Best regards,

Yuliana Gu

v-yulgu-msft this works great, thank you very much for taking the time to go through this.

I was a little concerned how this will react when I apply it to my real dataset which has over 300 locations, so I fiddled around a bit more and managed to come up with an alternative solution which I'm happy with.

I first created a measure to get the total tonnes collected by location in a given time period:

Sum of Tonnes = CALCULATE(SUM('Collection Database'[Collected Tonnes]))

I then checked to see if a given location has had an audit within the given time period:

Audit Exist? = DISTINCTCOUNT('Audit Database'[Location])

Finally I used a SUMX function to find the total tonnes from locations with audits in the given time period, but the trick was to use my Intermediate Table as the table in the SUMX function:

Sum Across = SUMX('Intermediate Table',[Sum of Tonnes] * [Audit Exist?])

It was the SUMX that I was missing, this also helped me solve the remaining issues in the rest of my calculation, being able to multiply across and then sum down rather than suming down and multiplying across like I would with SUM. Also having the Intermediate Table made this method possible.

The final file can be found here, I'm not sure how to attach it to the message.

Thanks again for the help!

Sam