Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
ezFlow
Frequent Visitor

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]) 

ezFlow_0-1636298169948.png


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?

1 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 :

vchenwuzmsft_0-1636513274443.png

 

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:

vchenwuzmsft_1-1636513501503.png

 

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.

View solution in original post

3 REPLIES 3
Greg_Deckler
Super User
Super User

@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.


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

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 

ezFlow_0-1636333233551.png

ezFlow_3-1636333638391.png


2. Second List Contains additional Defect Quantity field 

ezFlow_1-1636333419645.png

ezFlow_2-1636333600350.png

 

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

ezFlow_4-1636334123667.png

I hope this clarifys the outcome I wanted in  POWER BI

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 :

vchenwuzmsft_0-1636513274443.png

 

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:

vchenwuzmsft_1-1636513501503.png

 

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.

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.