Total calculated column that is pulling MAX quantity of many products

[KyleBlevins]

I am running into an issue where I want to grab a number from the database of a product's total produced for an order. What I am running into is that it is giving me that number, but not calculating it in the total line because I don't want to summarize it. Otherwise, it will give me too high a number.

So, what needs to happen... I need to summarize the # of defects column (successful), # of Comp column (unsuccessful), and Qty column (unsuccessful).

The # of defects column is COUNT(Query1[Comp_REF])
The # of Comp column is derived from a query that counts the items grouped by the PCB_NAME

The DPMO field is (([# of Defects] / ([# of PCB's] * RELATED(Query2[# of Comp]))) * 1000000)

The # of PCB's column is MAX((Query1[QTY]))

The QTY column is derived from a query that runs MAX(QTY) and groups by work_order

Any information needed to clear up my question, just ask.

[v-yulgu-msft]

Hi @KyleBlevins,

Please share the sample data stored in source table, no need to post all detailed records and columns, just post the necessary data. Please show us the relationship between Query1 and Query2, if it exists. Also, ou said "# of Comp column (unsuccessful), and Qty column (unsuccessful)", how did you write the formulas? Are those calculated values mentioned in above image measures or calculated columns?

Regards,

Yuliana Gu 

[KyleBlevins]

The relation between query 1 and query 2 is the name of the PCB. It will link both PCB names and group them. Then, it counts how many components are on each PCB. This gives you back the # of components per PCB.

This is needed to pull in the PCB_NAME column and count the # of components.

Query2 = SELECT d.PCB_NAME, count(C.Comp_ID) AS [# of Comp]
                FROM Components AS c RIGHT JOIN PCB AS d ON c.PCB_ID = d.PCB_ID
                GROUP BY d.PCB_NAME

This is needed to group by the current date and pull in all defect data for calculations.

Query1 = SELECT * FROM Defect_Table
                WHERE Month(Defect_Date) = Month(getdate())
                AND Year(Defect_Date) = Year(getdate())

The QTY column is pulling the quantity per order. Every line item in the defect tracker database has a quantity for the work order. It links to the work order. Then, this report groups everything by work order. So, you will see how many defects happened in each work order. How many components per PCB. How many PCB's were ordered on that work order.

[KyleBlevins]

bump

[KyleBlevins]

bump

[v-yulgu-msft]

Hi @KyleBlevins,

Please post the sample data stored in table view in desktop. In other words, sample data returned by Query1 and Query2. Please provide more necessary information referring to this blog so that I can reproduce your scenario in my environment.

Regards,

Yuliana Gu