cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
KyleBlevins Frequent Visitor
Frequent Visitor

Total calculated column that is pulling MAX quantity of many products

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

 

2017-11-30_144038.jpg

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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.

5 REPLIES 5
v-yulgu-msft Super Contributor
Super Contributor

Re: Total calculated column that is pulling MAX quantity of many products

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 

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
KyleBlevins Frequent Visitor
Frequent Visitor

Re: Total calculated column that is pulling MAX quantity of many products

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 Frequent Visitor
Frequent Visitor

Re: Total calculated column that is pulling MAX quantity of many products

bump

KyleBlevins Frequent Visitor
Frequent Visitor

Re: Total calculated column that is pulling MAX quantity of many products

bump

v-yulgu-msft Super Contributor
Super Contributor

Re: Total calculated column that is pulling MAX quantity of many products

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

PBI Community Highlights

PBI Community Highlights

Check out what's new in the Power BI Community!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 205 members 1,933 guests
Please welcome our newest community members: