I'm a fairly new Tableau user with little to no programming expertise and I'm trying to get past an aggregation problem that I've encountered since I first started using the software. I'm trying to get a calculated measure that I can apply to a dashboard.
Below is an altered example of part of a data source I'm using to build my dashboards.
In a nutshell, when rework needs to be done in our manufacturing process, we collect certain data elements that are tied back to a nonconformance record called the nonconformance serialized number (NCSN seen in column A). The measures data that we report on, are the amount of rework hours (Hours Spent seen in column B), and the number of nonconforming parts associated with the rework (NC Qty seen in column C).
For some rework operations, there may only be one rework step with Hours Spent and NC Qty tied back to a NCSN, as seen in Row 1. Getting the Hours Spent and NC Qty for this as a sum is easy. However, when multiple rework steps are needed, we capture the Hours Spent and NC Qty at each step along the way, referencing the same NCSN number. For example, if looking to Rows 24 thru 27, we see that there is a total of 13.24 Hours Spent and (if doing a SUM) 4 NC Qty.
This is where my problem begins: I’m trying to find a way to roll up the NC Qty respective to an NCSN without overstating the line item NC Qty. I want to report on an NC Qty of 1, and not as 4 as there was only 1 nonconforming part that was run through 4 different rework sequences. I’ve done searches on possible remedies for this but have yet to find anything. I have to imagine that Tableau has a way of doing this in a table calculation of some kind.
Does anyone know how to create a measure for NC Qty that’s tied to the NCSN without duplicating the NC Qty over several rows? As another example, using the below data, if I wanted to use the dimension of Process and only filtered for “Deburr”, the end goal would be to see the corresponding Hours Spent to be 2.18 and the NC Qty to be 14.
Any and all help and suggestions would be greatly appreciated. Once I understand the concept behind this, it will also allow me to do similar calculations against other fields in other data sets.
I've also included the Excel file for ease of use.
Thanks in advance for your assistance,
Tableau Rework Master_F.xlsx 216.2 KB