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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
KyleBlevins
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
Employee
Employee

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.

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.

bump

bump

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.