Simple Dax formula to multiply 2 columns from different tables based on matched criteria

[nocallaghan]

Hi all,

So I have 3 tables and their relevant columns:

• TonnageTable - Building_ID, Stream_ID, Tons
  • Contains the tonnages that each building produces for each material stream (trash, recycling, organics, etc).
• CompositionTable - Building_ID, Stream_ID, Composition_Percentage, GHG_Code
  • Contains a composition percentage for a set of material categories that sum up to 100% for each building and each stream.
• GHGTable - GHG_Code, GHG_PerTonLandfilled, GHG_PerTonRecycled
  • Contains the emissions for each material that 1 ton generates when either landfilled or recycled.

TonnageTable and CompositionTable are linked by a bridge table called TonnageCompBridge since there is a many-to-many relationship.

CompositionTable and GHGTable are linked by a column called GHG_Code which is the code for each material category.

I am trying to calculate the GHG Emissions that are produced based on the tons that are produced, with those tons are allocated to each stream, to each material category, and then turn those tonnages for each stream and material category into the sum of emissions they produce.

Put more technically,

I am trying to create a measure that multiplies the sum of the Tons column in the TonnageTable by each Composition_Percentage, and then multiply each of those numbers by the corresponding GHG_PerTonLandfilled after matching the GHG_Code from the CompositionTable and the GHG_Code from the GHGTable.

Lets say we have building X that produces 1000 tons of trash.

Our material categories, their GHG_Code, and the trash composition is as follows in our CompositionTable below:

1 - Paper - 25%

2 - Plastic - 25%

3 - Organics - 25%

4 - Other - 25%

Our GHGTable is below (values represented as MTCO2):

1 - Paper - 20 

2 - Plastic - 5

3 - Organics - 100

4 - Other - 250

I would want my measure to equal (1000*25%*20) + (1000*25%*5) + (1000*25%*100) + (1000*25%*250) = 93,750

I would greately appreciate any help or insight on this, thank you!

[johnt75]

Try

New Measure =
SUMX (
    TonnageTable,
    VAR Tons = TonnageTable[Tons]
    VAR BuildingID = TonnageTable[Building ID]
    VAR StreamID = TonnageTable[Stream ID]
    RETURN
        SUMX (
            CALCULATETABLE (
                CompositionTable,
                TREATAS (
                    { ( BuildingID, StreamID ) },
                    CompositionTable[Building ID],
                    CompositionTable[Stream ID]
                )
            ),
            VAR GHGCode = CompositionTable[GHG Code]
            VAR CompPct = CompositionTable[Composition Percentage]
            VAR Landfill =
                LOOKUPVALUE ( GHGTable[GHG_PerTonLandfilled], GHGTable[GHG Code], GHGCode )
            RETURN
                Tons * CompPct * Landfill
        )
)