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

[Samboko]

From the example tables below I am trying to sum the Collected Tonnes in Table 1 based on only those Locations that have had an audit (Table 2). So in this particular example, I would only be adding the Collected Tonnes from Toronto and Edmonton (140 tonnes) because they are the only ones with audits in Table 2. The number and location of audits will change over time so this needs to be a dynamic relationship. Furthermore I have Tables 1 and 2 linked by a third intermediate table (Table 3) which is a unique list of all possible Locations.

This also needs to be responsive to a date slicer, so if there was no audit for a particular location within the date range, the sum of Collected Tonnes would exclude that location.

I have tried a variety of methods with no luck, including trying to make a measure or a calculated column to check for existence of an audit. I'm sure there is a straightforward solution but it eludes me!

I've made up a .pbix file with the example here

Table 1 - Collection Database
Date	Location	Commodity	Collected Tonnes
24-Jan-18	Toronto	Fibre	50
15-Feb-18	Toronto	Containers	20
23-Feb-18	Winnepeg	Containers	30
28-Feb-18	Edmonton	Fibre	70
02-Mar-18	Vancouver	Fibre	85
15-Mar-18	Victoria	Containers	35
24-Mar-18	St. Johns	Fibre	100

Table 2 - Audit Database
Date	Location	Material	Audit Kg
15-Feb-18	Toronto	Office Paper	6
15-Feb-18	Toronto	Cardboard	8
15-Feb-18	Toronto	Plastic	2
15-Feb-18	Toronto	Residual	1
28-Feb-18	Edmonton	Office Paper	9
28-Feb-18	Edmonton	Cardboard	7
28-Feb-18	Edmonton	Plastic	3
28-Feb-18	Edmonton	Residual	2

Intermediate Table

Edmonton

St. Johns

Toronto

Vancouver

Victoria

Winnepeg

Thanks.

[v-yulgu-msft · Accepted solution]

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 · Accepted solution]

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

[Samboko · Accepted solution]

@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