Summarizing values from two tables

[ezFlow]

I have Two Tabes that I want ot summarize values from

Table1: 'Outgoing Inspections' - Fields: 'Outgoing Inspections'[Plant], 'Outgoing Inspections'[No. Helmets Inspected],'Outgoing Inspections'[Order Type],
Table2:  'QA Inspection Data' = Fileld: 'QA Inspection Data'[Defect Quantity]

Need to dispaly in a Table the Summary of Helmets Audit by Plant , Order Type, Helemets Audit and Defect Quantity (from the 2nd Table).
They are linked via the fields  Outgoing Inspections'[Id]  &  'QA Inspection Data'[OIID]

Based on another article, I was able to create a Temp Table that achive the first part.

Helmets QA Audit = SUMMARIZE( 'Outgoing Inspections', 'Outgoing Inspections'[Plant], 'Outgoing Inspections'[Id], 'Outgoing Inspections'[No. Helmets Inspected], 'Outgoing Inspections'[Order Type],  "Helmets Audit", SUM('Outgoing Inspections'[No. Helmets Inspected]) 

My Question is how to add the correcponding Defect Quantity values from the other table?
Also, is this the best practice to create a Temp table or can it be achieve using SUMX measures?

[Greg_Deckler]

@ezFlow So, the general answer would be to use ADDCOLUMNS but hard to be specific with the information provided. Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

[ezFlow]

Hi Greg,
I'll do my best clarify my question and desired outcome.
First the source is 2 SP lists, that I have connect to SP and have a 1 to many relatioshipt based on
ID and OI-ID fields
1. Main List - Contains the follwoing field: Plant, OrderType, and No of Helmets inspetced 

2. Second List Contains additional Defect Quantity field 

In Power BI I would like a Table that show the SUMs the Helemets Inspects, the sun Defect Quantity for it.  looking like.

I hope this clarifys the outcome I wanted in  POWER BI

[v-chenwuz-msft · Accepted solution]

Hi @ezFlow 

To get the value of Defect Quantity , ADDCOLUMN() function is good way to return the temp table of progress. Some thing like the following:

Helmets QA Audit (ADDCOLUMN) = 
VAR _TEMP =
    SUMMARIZE(
        'Outgoing Inspections',
        'Outgoing Inspections'[Plant],
        'Outgoing Inspections'[Order Type],
        'Outgoing Inspections'[Id],
        "Helmets Audit", SUM( 'Outgoing Inspections'[No. Helmets Inspected] )
    )
RETURN 
    ADDCOLUMNS(
        _TEMP,
        "Defect Quantity",
            CALCULATE(
                SUM( 'QA Inspection Data'[Defect Quantity] ),
                FILTER( 'QA Inspection Data', [OIID] = EARLIER( 'Outgoing Inspections'[Id] ) )
            )
    )

the result :

But in your case , also to get the table you expect, the following expression may be the best choise. This expression use the relationship maybe faster then above.

Helmets QA Audit (SUMMARIZE) = 
SUMMARIZE(
    'Outgoing Inspections',
    'Outgoing Inspections'[Plant],
    'Outgoing Inspections'[Order Type],
    "Helmets Audit", SUM( 'Outgoing Inspections'[No. Helmets Inspected] ),
    "Defect Quantity",
        CALCULATE(
            SUM( 'QA Inspection Data'[Defect Quantity] ),
            USERELATIONSHIP ( 'Outgoing Inspections'[Id], 'QA Inspection Data'[OIID] )
        )
)

result:

I put my pabix file in the attachment you can refer.

Best Regards

Community Support Team _ chenwu zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.