fliplid1992
New Member
- Joined
- Nov 13, 2015
- Messages
- 2
Hi Everyone,
This is my first time posting, so please excuse any accidental forum faux pas. I am trying to create a pivot table that summarizes how many parts remaining at each process sequence. Ideally, when it's complete it'll also be able to drill down on each sequence to see the number of scrap for each scrap code, the number of days those parts have been at that sequence, and the sales dollar value of that group of parts.
Just to start, I've been working on calculating the number of pieces remaining at each sequence. That part of the data is stored in 2 tables, LOAD and LABOR. The LOAD table has the number of pieces ordered with a row for each sequence of each work order. Since I couldn't combine 2 many-to-many relationships into 1 one-to-many, I created a column called "Key_ID" that concatenates the work order number and process sequence number joined by a "-". The LABOR table stores the date, number or pieces completed, number of pieces scrapped, and the scrap code (if applicable, otherwise "0"). The LABOR table has a row for each time an operator logged on to the job ("many" side of the one-to-many).
I'm trying to make the number of pieces remaining column calculate in the pivot table ([# pcs ordered] - [# pcs complete] - [# pcs scrapped]). However, the "calculated field" option is grayed out. I was almost able to do what I wanted in Powerpivot by creating a calculated field in the LOAD table with the DAX expression CALCULATE(), but I think I'm losing my relationship to the number of pieces for each scrap code in the LABOR table.
Furthermore, I'm lost when it comes to drilling down on each of the "pieces remaining" values to find the scrap details, number of days the work has sat stagnant, and the sales dollar value (I'm still working on relating the sales dollar table which is a separate, 3rd table in the relationship).
Please see below for screenshots of my data. All help is appreciated! Especially since I have been confused for weeks with this problem.
This is my first time posting, so please excuse any accidental forum faux pas. I am trying to create a pivot table that summarizes how many parts remaining at each process sequence. Ideally, when it's complete it'll also be able to drill down on each sequence to see the number of scrap for each scrap code, the number of days those parts have been at that sequence, and the sales dollar value of that group of parts.
Just to start, I've been working on calculating the number of pieces remaining at each sequence. That part of the data is stored in 2 tables, LOAD and LABOR. The LOAD table has the number of pieces ordered with a row for each sequence of each work order. Since I couldn't combine 2 many-to-many relationships into 1 one-to-many, I created a column called "Key_ID" that concatenates the work order number and process sequence number joined by a "-". The LABOR table stores the date, number or pieces completed, number of pieces scrapped, and the scrap code (if applicable, otherwise "0"). The LABOR table has a row for each time an operator logged on to the job ("many" side of the one-to-many).
I'm trying to make the number of pieces remaining column calculate in the pivot table ([# pcs ordered] - [# pcs complete] - [# pcs scrapped]). However, the "calculated field" option is grayed out. I was almost able to do what I wanted in Powerpivot by creating a calculated field in the LOAD table with the DAX expression CALCULATE(), but I think I'm losing my relationship to the number of pieces for each scrap code in the LABOR table.
Furthermore, I'm lost when it comes to drilling down on each of the "pieces remaining" values to find the scrap details, number of days the work has sat stagnant, and the sales dollar value (I'm still working on relating the sales dollar table which is a separate, 3rd table in the relationship).
Please see below for screenshots of my data. All help is appreciated! Especially since I have been confused for weeks with this problem.