Forum Discussion

ezFlow's avatar
ezFlow
Frequent Visitor
4 years ago
Solved

Summarizing values from two tables

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?

Need Help
  • v-chenwuz-msft's avatar
    v-chenwuz-msft
    4 years ago

    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.

3 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    4 years ago

    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's avatar
      ezFlow
      Frequent Visitor
      4 years ago

      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's avatar
        v-chenwuz-msft
        Community Support
        4 years ago

        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.